VLOOKUP Under VBA
 

I am working with a multi page spreadsheet where I would like to lookup data
on another sheet. If I were to develop fixed coding it would look as
follows:
VLOOKUP(A3,Reports!A2:W60000,13,0)
The problem is that this sheet would have hundreds of rows with each having
5-6 cells with various versions of this lookup. This equates to a lot of
lookup time when in 90% of the applications there will be less then 100
records in the source file(Report), but I need to prepare for the biggist
file I could encounter on the Reports sheet.
Todate, I have learned how to use FOR loops and index addressing to
determine the number of records in a file and limit my search to that area,
but I cannot figure out how to use index addressing with the lookup
function.
As always, any help would be appreciated,
Craig

VLOOKUP Under VBA
 

Hi

Dim lastrow as long, myRange as Range

With Sheets("Reports")
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

Set myRange= Range(Cells(2,1), Cells(lastrow,23))
End With

Vlookup(A3,myRange,13,0)

--
Regards

Roger Govier


"C Brandt" wrote in message
...
I am working with a multi page spreadsheet where I would like to lookup
data
on another sheet. If I were to develop fixed coding it would look as
follows:
VLOOKUP(A3,Reports!A2:W60000,13,0)
The problem is that this sheet would have hundreds of rows with each
having
5-6 cells with various versions of this lookup. This equates to a lot
of
lookup time when in 90% of the applications there will be less then
100
records in the source file(Report), but I need to prepare for the
biggist
file I could encounter on the Reports sheet.
Todate, I have learned how to use FOR loops and index addressing to
determine the number of records in a file and limit my search to that
area,
but I cannot figure out how to use index addressing with the lookup
function.
As always, any help would be appreciated,
Craig