Excel VBA Macro for vlookups

[maca1008]

Hey, i'm trying to run a macro with a vlookup in sheet 3. the reference table for the vlookup is in sheet 1. this macro will be run on a daily basis, with a new set of data in sheet 1 each day, hence the size of the table will change. currently the line of vba code is shown below:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'sheet 1'!R3C1:R7C3,2,0)"

the size of this table is from cell A3:C7, however this size will change. is it possible to edit the above line of code, so that it selects the table from:

range("A3").end(xlright).end(xldown).select

I am not sure how to convert from the R1C1 format into the A1 format. any help would be greatly appreciated.

Thank you

 

[Stoneboysteve]

maca1008,

'This code sets focus on a cell within the range:
Range("A3").Select

'This code selects the contiguous data table:
Selection.CurrentRegion.Select

'This code gives the selected contiguous data range a name:
ActiveWorkbook.Names.Add Name:="tempName", RefersToR1C1:= _
"=Sheet1!R1C1:R30C11"

'You can use the named range in Vlookup: =vlookup(LookFor,NamedRange,Colums,Sorted)

Stoneboysteve

 

[maca1008]

Stoneboysteve,

Thanks for your help. Those changes will still restrict the size of the vlookup to the number of rows in the selected region. The number of rows in the selected region will change on a daily basis, so is it possible to replace this line of code:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'sheet 1'!R3C1:R7C3,2,0)"

with a line of code that will select the table in sheet 1 regardless of size; for example; a code where specific cell values are not required? if this is possible by replacing the red text that would be a great help.

Thank you,

maca1008

 

[Stoneboysteve]

maca1008,

Once focus is set in the table in sheet 1, the Selection.CurrentRegion.Select command gets the whole table, regardless of how big or small.

Catching that new region with a named range, and using the named range in the red text in your formula will give you the whole table each time.

You can put the formula in a macro and run the macro each day. You may need to add a line to remove the named range ahead of the formula:
ActiveWorkbook.Names("tempName").Delete

Stoneboysteve