PDA

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



Nbisgaard
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


NextCode:
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
Loop


MsgBox "Task Complete!"


ResetSettings:
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"

input:

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?

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