Run-time error 1004 when trying to insert VLOOKUP in cell

[hagea_dan]

Hi,

I'm trying to insert in a range a VLOOKUP formula that will return data from another closed workbook but the line where the formula is inserted returns error 1004

Here is the code:

Sub insFormula()
Dim c As Range
Dim LastRow As Integer


With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each c In .Range("A2:A" & LastRow)
        c.Offset(0, 4) = "=VLOOKUP(" & c.Address(0, 0) & ";'C:\Users\Dan\Desktop\[stoc.xlsx]stoc total'!B:I;8;0)"
    Next c


End With


End Sub

If I manually enter the formula and replace the dynamic cell address part it works.
Can someone please tell me what is the problem with this code?

 

[MARK858]

Are you sure that your separators are ; as it works fine for me if I change them to commas, I get...

=VLOOKUP(A2,'C:\Users\Dan\Desktop\[stoc.xlsx]stoc total'!B:I,8,0)

 

[hagea_dan]

Are you sure that your separators are ; as it works fine for me if I change them to commas, I get...

My separator when entering a formula manually is semi-colon. I guess it has something to do with my region.
I've replaced them in the code with commas as you said and it works but if I check the formula in the formula bar the separator is still semi-colon. Very strange.
Thank your for your help.