CODEWITHSUNDEEP
vbavlookup
KingKong
KingKong

Reputation: 421

Using an entire sheet as the VLOOKUP table array

I have a worksheet that is comprised entirely of just data. The worksheet is declared & set in code and I am keying off the first column in the sheet as well.

I tried it and got an error, but was wondering if this is possible. Can I set that entire sheet as a range that can be passed into the VLOOOKUP?

.Cells(row, col) = WorksheetFunction.VLookup("key", sheetname, [SomeCol].Column, 0)

Error is 1004: Unable to get the VLOOKUP property of the WorkSheetFunction class.

Upvotes: 0

Views: 5137

Answers (1)

Tim Williams
Tim Williams

Reputation: 166540

If the data is a contiguous table (with no empty rows or columns) then (e.g) 

Range("A1").CurrentRegion

will return the range which contains data.

.Cells(row, col) = WorksheetFunction.VLookup("key", _
               Worksheets("sheetName").Range("A1").CurrentRegion, _
               colNum, False)

FYI the approach above will cause a run-time error if the vlookup doesn't find a match. This version avoids that by dropping the WorksheetFunction:

Dim v

v = Application.VLookup("key", _
               Worksheets("sheetName").Range("A1").CurrentRegion, _
               colNum, False)

.Cells(row, col) = IIf(IsError(v), "Not found", v)

Upvotes: 1

Related Questions