Using VBA to insert formulas

[no_Fate]

Hi all,

I'm trying to use VBA to insert the formula below into a cell but I always get a runtime error 1004.

Sub Fill_Formulas()
     
    ThisWorkbook.Worksheets(4).Range("D2").Formula = "=IFERROR(VLOOKUP($A$2;'[Daily Tracker.xlsx]Info'!$A:$I;2;FALSE);"")"
    
End Sub

What am I doing wrong?
Thank you in advance!

 

[L. Howard]

Hi all,

I'm trying to use VBA to insert the formula below into a cell but I always get a runtime error 1004.

Sub Fill_Formulas()
     
    ThisWorkbook.Worksheets(4).Range("D2").Formula = "=IFERROR(VLOOKUP($A$2;'[Daily Tracker.xlsx]Info'!$A:$I;2;FALSE);"")"
    
End Sub

What am I doing wrong?
Thank you in advance!

Try it with double quotes.

ThisWorkbook.Worksheets(4).Range("D2").Formula = "=IFERROR(VLOOKUP($A$2;'[Daily Tracker.xlsx]Info'!$A:$I;2;FALSE);"""")"

Howard

 

[Osvaldo Palmeiro]

And try replacing semicolon ( by comma (,) in the formula

 

[L. Howard]

And try replacing semicolon ( by comma (,) in the formula

@Osvaldo Palmerio, I assumed the OP is using a European version of Excel, where the semicolon is standard.

Howard

 

[Osvaldo Palmeiro]

Hi, Howard.
Thanks for that, I realy did not consider that possibility.

 

[no_Fate]

Hi @Osvaldo Palmeiro and @L. Howard,

Thank you both! This is working just fine now with your suggestions.
I still don't understand the comma/semicolon since on the spreadsheets I actually need to use ; in the formulas... and that's what the VBA is pasting there. Whatever!

Cheers!

 

[Osvaldo Palmeiro]

@Osvaldo Palmerio, I assumed the OP is using a European version of Excel, where the semicolon is standard.
Howard

@no_Fate
I think you could use semicolon since you use FormulaLocal (see below), but using just Formula you should use colon besides your version beeing European.


this should work

ThisWorkbook.Worksheets (4) .Range ("D2") FormulaLocal = "=IFERROR(VLOOKUP($A$2;'[Daily Tracker.xlsx]Info'!$A:$I;2;FALSE);"""")"