Address formula in VBA with variables

[dutchmenqb]

Hello,

Struggling with the following formula. I'm assuming my issue is with the variables in the Row, Column, and Match areas since the formula works just find when I use a specific range on Excel itself. However, the row number needs to change so I'm stuck needing to use a variable. How should I be entering the variables into this formula?

cellNo2 is a row number found with a similar formula earlier in the code.

=ADDRESS(ROW(B & cellNo2 & :Q & cellNo2), COLUMN(B & cellNo2 & :Q & cellNo2) + MATCH(T1,B & cellNo2 & :Q & cellNo2, 0) - 1)

Thank you

 

[bhos123]

Try

=Indirect("ADDRESS(ROW(B"&cellNo2&":Q"&cellNo2&"),COLUMN(B"&cellNo2&":Q"&cellNo2&") + MATCH(T1,B"&cellNo2&":Q"&cellNo2&", 0) - 1)"

 

[dutchmenqb]

I'm getting an Expected: end of statement error and it is taking me to Address...

Is there another place I need to have quotation marks?

 

[bhos123]

=ADDRESS(ROW(INDIRECT("B"&CellNo2):INDIRECT("Q"&CellNo2)), COLUMN(INDIRECT("B"&CellNo2):INDIRECT("Q"&CellNo2)) + MATCH(T1,INDIRECT("B"&CellNo2) :INDIRECT("Q"& CellNo2), 0) - 1)

 

[dutchmenqb]

Same error - would it work better if I made column B and column Q variables as well?

 

[bhos123]

do you have Cellno2 assigned in your data file? if yes you should get a address as results(like $B$2) or Error(#N\A). If you are getting N\A means MATCH is resulting #N\A

 

[dutchmenqb]

I adjusted the formula to the following:

=ADDRESS(ROW(INDIRECT(cellNo4):INDIRECT(cellNo7)), COLUMN(INDIRECT(cellNo4):INDIRECT(cellNo7)) + MATCH(T1,INDIRECT(cellNo4):INDIRECT(cellNo7), 0) - 1)

cellNo4 is $B$23 in the case I'm using for testing, and cellNo7 is $Q$23 in the case I'm using for testing. This formula did not result in an error in VBA, but when VBA entered the formula in Excel, it resulted in a #NAME? error because it pasted the formula exactly the same in Excel as was written in VBA, not replacing the variables with cells.

I currently have the cellNo4 and cellNo7 variables dimensioned as Strings, and the cells they are referencing are based off another Address function (that does work) and two concatenate functions.