Cell Reference till the last Row

1
votes

Can any one help me on the below code.

I'm trying to find if a cell contains any of the text that are in the given Cell range. And then return the text that is found. And its working fine if i give cell range like B4:B6 but i want it to be dynamic till the last row in B column.

Formula Used in the cell

IFERROR(LOOKUP(1,0/COUNTIF($H2,"*"&Requirement!$B$2:$B$4&"*"),Requirement!$B$2:$B$4),"No Data")

VBA Scrpit

ActiveCell.FormulaR1C1=_"=IFERROR(LOOKUP(1,0/COUNTIF(RC8,""*""&Requirement!R2C2:R4C2&""*""),Requirement!R2C2:R4C2),""No Data"")"

Here instead of Requirement!$B$2:$B$4, i want it to be till the last row.

1
excelvba
For situations such as yours, I've always preferred using a dynamic named range. You can see an example here. - K.Dᴀᴠɪs

1 Answers

1
votes

This depends on if you want the last row in the sheet or the last row that contains data. The general solution is relatively simple, you replace

"R2C2:R4C2"

with

"R2C2:R" & Rows.Count & "C2"

This provides you with the last row in the sheet. If you want the last row that contains data in column B, then use:

"R2C2:R" & ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row & "C2"

Use this to replace all relevant cell references in your code.