Macros - Vlookups & Ranges

[geoffcraver]

Hello,

I'm working on a macro that performs vlookups. The issue I have is that both the lookup value and table array needed for the vlookup will be variable lengths every time.

So if I have a 'Summary' tab where my lookup value starts in A2, how do I stop the vlookup when it hits a blank cell in column A?

Also how do I create a dynamic range for the table array on the 'data' tab?


Thank You

 

[Jonmo1]

Welcome to the board...

Here's some common code to find the last non empty cell in a column
LastRow = Cells(Rows.Count,"A").End(xlup).Row

Then you can use that in a range like
Range("A1:A" & LastRow)

Hope that helps.

 

[geoffcraver]

I appreciate the help Jonmo. This might be a dumb follow up.
How do I make a dynamic range absolute? Normally you can just say $A$2:$B$20, but how do I absolute the range when its ("A1:B"&LastRow)?


Thanks

 

[cgmojoco]

A manual process...you can try making a named range.
Select the sheet with your range then from the menu (Insert, Name, Define)

Name your range (Say Dynamic_Range1) and for the refers to use the below code:

 =OFFSET(URPAGE!$A$1,0,0,COUNTA(URPAGE!$A:$A),COUNTA(URPAGE!$1:$1))

Replace URPAGE with the name of your sheet...

Note:
This will make your range dynamic for rows AND columns.
When you replace URPAGE with your sheetname, do outside of the refers to area as while you type it sometimes automatically changes your formula to something you cannot use.
Sheet names may not be able to have spaces. (from my experience in xl 2000)

Now you can refer to Dynamic_Range1 and when you do it will pickup rows and columns dynamically.

 

[geoffcraver]

I appreciate the help. Both of these worked for me.

Thanks