CODEWITHSUNDEEP
vbaexcel
Vegard
Vegard

Reputation: 4952

Range.Formula application-defined error

objSheet.Range("L20").Formula = _
   "=INDEX(Sheet2!B2:B4824;MATCH(Sheet!F16;Sheet2!A2:A4824; 0))"

Why does this line fail? I've tried both static and dynamic cell references. I've tried Range.Formula, Cells.Value, etc... to no avail. I even made a separate function that constructs the string so I can invoke the variable instead of the direct string, but no dice. Also tried stating the range several different ways.

The sheets are named correctly (the formula works without hiccup when manually inserted) and objSheet is defined (the rest of the macro works fine). Removing objSheet does nothing.

What on earth am I missing?

Upvotes: 1

Views: 559

Answers (1)

Sam
Sam

Reputation: 948

You are using semi colons. You need to use commas.

objSheet.Range("L20").Formula = "=INDEX(Sheet2!B2:B4824;MATCH(Sheet!F16;Sheet2!A2:A4824; 0))"

Should be

objSheet.Range("L20").Formula = "=INDEX(Sheet2!B2:B4824,MATCH(Sheet!F16,Sheet2!A2:A4824, 0))"

Upvotes: 2

Related Questions