Error 1004 when inserting lookup formula in Excel 2000

Question

I've got the following code to look up the date at which a value occurs in a separate sheet in Excel 2000:

sSheet.Range(dateColumn & pRow) = _
    "=lookup(" & valColumn & pRow & ";'" & _
    iSheet.Name & "'!" & tColumn    & firstRow & ":" & tColumn    & lastRow & ";'" & _
    iSheet.Name & "'!" & dateColumn & firstRow & ":" & dateColumn & lastRow & ")"

This fails with error 1004, which seems to be a complete catch-all. The real kicker is that if I insert a tick mark before the equals sign in the value, as in "'=lookup(", it works fine, and if I remove the tick mark manually afterwards, the formula works. So why is it refusing to be inserted?

Answer 1

You need to specify it as a formula

sSheet.Range(dateColumn & pRow).Formula = _
"=lookup(" & valColumn & pRow & ";'" & _
iSheet.Name & "'!" & tColumn    & firstRow & ":" & tColumn    & lastRow & ";'" & _
iSheet.Name & "'!" & dateColumn & firstRow & ":" & dateColumn & lastRow & ")"

Answer 2

Turns out the problem is that VBA doesn't respect the localization settings when creating formulas - I had to use commas instead of semicolons to separate the lookup function arguments.