CODEWITHSUNDEEP
excelvba
Bart Koolhaas
Bart Koolhaas

Reputation: 351

Inserting formula results in Run-time error 1004

I have a vba script which inserts some formulas. For some reason, it refuses to insert a VLOOKUP function; it gives a Run-time error 1004: Application-defined or object-defined error.

My code is as follows: 

Worksheets("Sheet1").Cells(row, 16).Formula = "=vlookup(O" & row & ";Sheet2!$A$2:$H$250;5;FALSE)"

I've been trying all kinds of variations to figure out what is triggering the error code. If I try the nonsense formula

Worksheets("Sheet1").Cells(row, 16).Formula = "=vlookp(O1)"

It does not generate an error and places the formula in the respective cell. If I change just one letter, by making vlookp(01) into vlookup(01) it generates the error again.

Any idea what is going on and what causes this error?

Txs!! Bart

Upvotes: 2

Views: 2115

Answers (3)

iDevlop
iDevlop

Reputation: 25252

Not a straight answer, but a way to solve your issue:
enter the formula manually in cell P2 (or any other cell), then press ctrl+G to go to the debug window, and type print range("p2").formula.
That should give a precise idea of what Excel expects the formula to look like in VBA.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

You are using ; as a delimiter for your formula, Excel is very English oriented, and it likes to find , in the formula.

So change your formula to:

"=vlookup(O" & Row & ",Sheet2!$A$2:$H$250,5,FALSE)"

Or change:

Worksheets("Sheet1").Cells(Row, 16).Formula

to:

Worksheets("Sheet1").Cells(Row, 16).FormulaLocal

Upvotes: 4

user6432984
user6432984

Reputation:

Replace the semicolons in your formula with commas.

Worksheets("Sheet1").Cells(Row, 16).Formula = "=VLOOKUP(O" & Row & ",Sheet2!$A$2:$H$250,5,FALSE)"

Upvotes: 3

Related Questions