Address formula in VBA with variables

dutchmenqb

New Member
Joined
Jul 6, 2016
Messages
19
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try

=Indirect("ADDRESS(ROW(B"&cellNo2&":Q"&cellNo2&"),COLUMN(B"&cellNo2&":Q"&cellNo2&") + MATCH(T1,B"&cellNo2&":Q"&cellNo2&", 0) - 1)"
 
Upvote 0
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?
 
Upvote 0
=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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top