using Vlookup in code

[sugargenius]

I need to scan through a list of names and find there resource code in
a lookup table. The macro and the lookup table itself aren't in the
actual file I'm processing. I've defined the lookup table in the
workbook where the macro resides. The table looks like this:

SMITH, JUDY MECH. ENGINEER IV
DOE, JOHN ADMIN ASST I

I created a workbook level name, "RES_CODE_LOOKUP", that refers to the
lookup table.

My first attempt at the macro:

------begin code---------
Sub LookupNames()
Dim lngLastRow As Long, x As Long, sResCode As Variant,
rngRescodeLookup As Range

On Error GoTo err
Set rngRescodeLookup =
Range("labor_actuals_macro.xls!RES_CODE_LOOKUP")

'delete header row
ActiveSheet.Range("A1").EntireRow.Delete

'determine last row
lngLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

'insert colum for cecode
ActiveSheet.Columns(7).EntireColumn.Insert

'loop through each row and lookup name in lookup table
For x = 1 To lngLastRow
sResCode = Application.WorksheetFunction.VLookup(Cells(x,
6).Value, rngRescodeLookup, 2)
Next x

Exit Sub
err:
MsgBox "Error" & err & ": " & Error(err)
End Sub
------end code---------

I get to the part where the vlookup and it returns a value on the
current sheet...not the value from the lookup table in the other book.
What am I doing wrong?

Thanks,
Woody

 

[Dave Peterson]

Sub LookupNames()
Dim lngLastRow As Long
dim x As Long
dim sResCode As Variant
dim rngRescodeLookup As Range

Set rngRescodeLookup _
= workbooks("labor_actuals_macro.xls") _
.names("RES_CODE_LOOKUP").referstorange

'delete header row
ActiveSheet.Range("A1").EntireRow.Delete

'determine last row
lngLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

'the lastused cell may not be what you think it is.
'maybe just using the last cell in column F???
With ActiveSheet
lngLastRow = .Cells(.Rows.Count, 6).End(xlUp).Row
End With

'insert colum for cecode
ActiveSheet.Columns(7).EntireColumn.Insert

'loop through each row and lookup name in lookup table
For x = 1 To lngLastRow
sResCode _
= Application.VLookup(Cells(x,6).Value, rngRescodeLookup, 2, false)
if iserror(srescode) then
cells(x,7).value = "missing"
else
cells(x,7).value = srescode
end if
Next x

End Sub


One of the differences is in the =vlookup(). Since you were matching on names,
I would expect that you wanted an exact match. That False as the 4th parm does
that.

I also used application.vlookup instead of
application.worksheetfunction.vlookup. The biggest difference there is that
application.vlookup will return an error that can be tested if there is no
match.

Application.worksheetfunction.vlookup will cause a run time error that you'd
have to trap.

(And I find the application.vlookup() much easier to code.)

 

[sugargenius]

Thanks Dave for your suggestions. It turns I had made a sheet level
name, so I think it was using the range on the current sheet to lookup
in. I was thinking Sheet1!$A$1 was sheet level name.

I like your way of referring to the range so I can use constants for
macro book name and lookup range name. I was doing something like:
Set rngRescodeLookup = Range("prep_labor_actuals.xls!RES_CODE_LOOKUP")

thanks,
Woody

 

[sugargenius]

Thanks Dave for your suggestions. It turns I had made a sheet level
name, so I think it was using the range on the current sheet to lookup
in. I was thinking Sheet1!$A$1 was sheet level name.

I like your way of referring to the range so I can use constants for
macro book name and lookup range name. I was doing something like:
Set rngRescodeLookup = Range("prep_labor_actuals.xls!RES_CODE_LOOKUP")

thanks,
Woody

 

[Dave Peterson]

If I know where the range should be, I'd use this:

Set rngRescodeLookup = workbooks("prep_labor_actuals.xls") _
.worksheets("sheet1").range("RES_CODE_LOOKUP")

I find that much easier than having to parse and build the string myself.