using Vlookup in code
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 |
using Vlookup in code
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 wrote: 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 |
using Vlookup in code
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 |
using Vlookup in code
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 |
using Vlookup in code
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. sugargenius wrote: 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 |
All times are GMT +1. The time now is 06:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com