question on VBA syntax

[bentley1]

I have an array formula in VBA:

For x = 1 To MatChgNum + 1
 
    Cells(13 * x + 3, 2).Select

Selection.FormulaArray = _
        "=INDEX(DB!R6C1:R500C1,SMALL(IF(DB!R6C2:R500C2=""m"",ROW(DB!R6C2:R500C2),""""),[B]x[/B])-ROW(DB!R6C2:R500C2)+1)"

when this outputs in the corresponding cell instead of 1 it comes out as x in the formula which is resulting in a name error. I think this is because the x is in quotes but is there a way to work around this?

 

[Jonmo1]

You have to take the x OUT of the formula string, and concatenate the value of x into the string..

Try

Selection.FormulaArray = _
"=INDEX(DB!R6C1:R500C1,SMALL(IF(DB!R6C2:R500C2=""m"",ROW(DB!R6C2:R500C2),"""")," & x & ")-ROW(DB!R6C2:R500C2)+1)"

 

[bentley1]

Hi Jonmo1,

thanks for the reply! sorry had to go for a bit but will try now

 

[bentley1]

great it works nicely thanks!!

 

[Jonmo1]

Glad to help, thanks for the feedback.