CODEWITHSUNDEEP
vbaexcelexcel-formula
harry71152
harry71152

Reputation: 17

Syntax error for INDEX formula using Macro

I'm getting a syntax error when I try to insert a formula in a cell using Macro. I can't seem to figure out what am I doing wrong? The formula works fine when I manually enter it. Here's my code:

ws3.Range("F2:F" & lastRow3).Formula = "=INDEX($L$1:INDEX(L:L,MATCH("ZZZ",D:D)),AGGREGATE(15,6,ROW($K$1:INDEX(K:K,MATCH("ZZZ",D:D)))/($K$1:INDEX(K:K,MATCH("ZZZ",D:D))=D2),COUNTIF($D$1:$D2,D2)))"

ws3 is the worksheet, and lastRow3 is just to autofill the contents by comparing to a column to the left. Any ideas why this is giving an error?

FYI: This formula is finding values in one column from another column and giving the adjacent entries.

Upvotes: 0

Views: 159

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

When filling a full range it is best to use R1C1 format.

Also with using vba to set the last row there is no reason for all the INDEX(L:L,MATCH("ZZZ",D:D)) to find the last row, since the code itself can set it directly.

Use this:

ws3.Range("F2:F" & lastRow3).FormulaR1C1 = _
    "=INDEX(R1C12:R" & lastrow & "C12,AGGREGATE(15,6,ROW(R1C11:R" & lastrow & "C11)/(R1C11:R" & lastrow & "C11=RC[-2]),COUNTIF(R1C4:RC4,RC[-2])))"

Upvotes: 1

Related Questions