Set variable to value returned by complex array formula

[jimrood]

I'm attempting to set a variable in VBA to the value returned when executing this array formula during a loop:
{=INDEX(Tbl_Findings[RvwrLen],MATCH(1,(Tbl_Findings[Intake Number]=TEXT($B$2,"0"))*(Tbl_Findings[Question#]=A6),0))+2}

I've attempted to use the Evaluate method but can't seem to get the syntax correct. Cell reference $B$2 is fixed in my worksheet, whereas A6 can be replaced by my variable Q.

Dim Q as Integer

For Q = 1 to 25

Any help would be greatly appreciated.

 

[sheetspread]

First see if you can print the formula text to a cell without the equal sign and curly braces, then add those on the worksheet. If ok, then try again with = and .formulaarray in the code. Finally, add =evaluate("formula here"). The formulaarray property is not needed for the last step.

 

[jimrood]

The formula works fine on the worksheet when contained in a cell. When I attempted to paste into the code it immediately flagged a problem with the quotes around the 0 in the Text function. I corrected it as follows: RebutStart = Evaluate("INDEX(Tbl_Findings[RvwrLen],MATCH(1,(Tbl_Findings[Intake Number]=TEXT($B$2,""0""))*(Tbl_Findings[Question'#]=Q),0))+2"). When I ran the macro it triggered a debug on this line.

 

[sheetspread]

Did you enter the formula to the cell on the worksheet manually or via the code?

The extra quotes should be enough, but I would enter the formula from vba without an equal sign just to see how the string looks.

 

[jimrood]

The formula is entered into the cell manually. I'm trying to avoid having it on the worksheet. I think I discovered the source of the debug issue though. It doesn't like the variable Q in the evaluate method. Cell A6 contains the question number but since I'm using this array in a loop to cycle through questions 1-25 I'm not sure how to specify the required criteria.

 

[sheetspread]

I'm trying to avoid having it on the worksheet.

Just place it there for testing purposes, or on a copy

 

[jimrood]

I got it to work! I assigned my variable Q to a static cell on the worksheet as it went through the loop and used that cell in my evaluate method formula. Thanks for you help with this.