Syntax error for INDEX formula using Macro

Question

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.

This works too, but I guess this makes excel unstable. It takes a long time to execute. — harry71152
– harry71152, Commented Apr 29, 2016 at 18:17

Scott Craner · Accepted Answer · 2016-04-29 18:23:16Z

1

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])))"

edited Apr 29, 2016 at 18:23

answered Apr 29, 2016 at 18:02

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Scott Craner Over a year ago

@harry71152 okay, it is a result. copy and paste what the formula is in F2, the one that the code put in there. Paste it in the comments here.

harry71152 Over a year ago

INDEX($L$1:$L$55,AGGREGATE(15,6,ROW($K$1:$K$55)/($K$1:$K$55)=D2),COUNTIF($D$1:$D2,D2))

Scott Craner Over a year ago

@harry71152 i had a ) in the wrong place. See edit.

Collectives™ on Stack Overflow

Syntax error for INDEX formula using Macro

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related