VLookUp in VBA

[raj]

i have 3 sheets
one input for records
one for a output after formatting input records
one for a lookup table

i need to use the look up table to tranform some values and output to the
output sheet
i can t get the vlookup working

in the sheet with the lookup table i have the range defined (which is
A2:b11) as NAICLookUp

here is the code


i have the sheet defined as
Dim NAICCodeTranslation As Worksheet '//NAIC code translation worksheet
Worksheets("NAIC code translation").Activate
Set NAICCodeTranslation = ActiveSheet
' code to active and set other work sheets

codenewval = Application.WorksheetFunction.VLookup(codeval, Sheets("NAIC
Code Translation").Range("NAICLookup"), 2, False)
i am getting subscript out of range error with the above (the sheet has
spaces in its name)

I also tried

codenewval = Application.WorksheetFunction.VLookup(codeval,
NAICCodeTranslation.Range("A2:B11"), 2, False)
and i get error saying "unable to get the VLookUp property of the
worksheetfunction class"

The table is basically name value pair
table A is name that i look up to get the value from table b

thanks

 

[George Nicholson]

You are trying to pass Vlookup a Range object. That isn't what it wants. It
wants a string: the address of the range, not the range itself.

Vlookup Help says this argument can be a "range name or a reference to a
range", but keep in mind that this is a *Worksheet* function. In that
context "reference to a range" *can't* mean a reference to a VBA range
object (since there is no way to enter that into a cell formula). It means a
"A1:C5" or "MyNamedRange" type range reference.

So, just try adding Address to what you have:
Sheets("NAIC Code Translation").Range("NAICLookup").Address

HTH,

 

[Chip Pearson]

You are trying to pass Vlookup a Range object. That isn't what

it wants. It wants a string: the address of the range, not the
range itself.

That is completely wrong. Vlookup needs a range object, NOT the
address of the range. E.g,

Result = Application.VLookup(3, Range("A1:B5"), 2, False)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

You'll get that error if no match is found for the VLOOKUP.
Change your code to something like

Dim Result As Variant
Result = Application.VLookup(codeval,
NAICCodeTranslation.Range("A2:B11"), 2, False)
If IsError(Result) = True Then
Debug.Print "Not found"
Else
Debug.Print "found: " & Res
End If


Note that there is no WorksheetFunction between Application and
VLookup.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

Note that there is no WorksheetFunction between Application and

VLookup.

I guess I should clarify this. There are two ways to call VLOOKUP
from VBA. The first, without the WorksheetFunction object,

Dim Result As Variant
Result = Application.VLookup(3, Range("A1:B5"), 2, False)

will return an Error type Variant to the result if no match is
found. The result must be declared As Variant, and you can test
it with

If IsError(Result) = True Then
Debug.Print "not found"
Else
Debug.Print "found: " & Res
End If

Note that if no match is found, no runtime error is raised.

The second way to use VLOOKUP is to include WorksheetFunction.
E.g.,

Result = Application.WorksheetFunction.VLookup(3, Range("A1:B5"),
2, False)

If no match if found, a runtime error 1004 is raised. In this
case, you need some error handling:

Dim Res As Variant
On Error Resume Next
Res = Application.WorksheetFunction.VLookup(1, Range("A1:B5"), 2,
False)
If Err.Number <> 0 Then
Debug.Print "Not Found"
Else
Debug.Print "Found: " & Res
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com