View Full Version : [SOLVED] Specific formulas not working in Loop_all_files_in_folder macro

07-23-2015, 03:37 AM
I have a macro to loop all the files in a folder, which works, and looks like this:

Sub ALLE_FILER_I_MAPPE()Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With

myPath = myPath
If myPath = "" Then GoTo ResetSettings

myExtension = "*.xls"

myFile = Dir(myPath & myExtension)

Do While myFile <> ""
Set wb = Workbooks.Open(Filename:=myPath & myFile)

wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"

'Save and Close Workbook
wb.Close SaveChanges:=True

myFile = Dir

MsgBox "Task Complete!"

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

If i instead of

wb.Worksheets(1).Range("D3").Formula = "=[data_til_dokumentation.xlsx]PI!$B$1"


wb.Worksheets(1).Range("N10").Formula = "=INDEX([data_til_dokumentation.xlsx]LS!$C$11:$C$18;MATCH(M10;[data_til_dokumentation.xlsx]LS!$B$11:$B$18;0)+0)"

I get an error Run-time error '1004':

I have checked that the formula i input actually do work when i copy paste it manually into the cell in question.

What am i doing wrong?

07-23-2015, 04:44 AM
Replace the semicolons with commas in the formula when using the .Formula property (it expects US notation).