Changing the formula of cell in all the sheets of the archive

Question

I have an Excel archive that contains about 100 sheets i want write VBA macro so that cell A1 of each sheet will contain this formula:

=RECHERCHEV(B2;'SUMUP'!B$1:H478;3;FAUX)

This is my macro

Sub WorksheetLoop()

     ' Declare Current as a worksheet object variable.
     Dim Current As Worksheet

     ' Loop through all of the worksheets in the active workbook.
     For Each Current In Worksheets

     'Change the formula of cell A1 
        Current.Range("A1").Formula ="=RECHERCHEV(B2;'SUMUP'!B$1:H478;3;FAUX)"
     Next
End Sub

When i execute this macro i got error of execution '1004', Note that SUMUP is a sheet of my archive

Unknown Unknown · Accepted Answer · 2015-09-15T13:53:22

VBA is very EN-US-centric. The Range.Formula property expects input as EN-US; you can use the Range.FormulaLocal property if you want to use the non-EN-US Office language pack FR-FR regionals.

Use one of these:

Current.Range("A1").Formula = "=VLOOKUP(B2,'SUMUP'!B$1:H478, 3, False)"
Current.Range("A1").FormulaLocal = "=RECHERCHEV(B2;'SUMUP'!B$1:H478;3;FAUX)"

Current.Range("A1").FormulaLocal = "=REMPLACER(CELLULE(""filename"";A1);1;TROUVE(""]"";CELLULE(""filename"";A1));"""")"

Changing the formula of cell in all the sheets of the archive

1 Answers