Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup VBA code | Excel Discussion (Misc queries) | |||
VLOOKUP as a vb code | Excel Discussion (Misc queries) | |||
Using VLOOKUP in VBA code | Excel Worksheet Functions | |||
what is the VBA code for VLOOKUP? | Excel Programming | |||
Help with Vlookup code | Excel Programming |