Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data in external workbook
Hello:
I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description Dim MyRange As range Dim LastRow As Long Dim rng As range With Sheets("PlantAnalysis") 'find last row in column A LastRow = sh.range("A" & Rows.Count).End(xlUp).Row 'set range to loop thru Set MyRange = .range("A3:A" & LastRow) For Each rng In MyRange 'test if rng is empty, if so skip it If Not IsEmpty(.Cells(rng.Row, "A")) Then .cells(rng.row,"B3:B")= Application.vlookup(rng,'C:\[ItemMaster.xls List'!"$A:$C",3,FALSE) End If Next range End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data in external workbook
First, the table file in your =vlookup() formula has to be open if the code is
going to work. And then you'd use something like: .cells(rng.row,"B3:B")= Application.vlookup(rng, _ workbooks("ItemMaster.xls").worksheets("list") _ .range("A:C"), 3, false) if the itemaster.xls workbook won't be open, then I'd just plop the formula into the cell (and convert it to values????). Ray wrote: Hello: I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description Dim MyRange As range Dim LastRow As Long Dim rng As range With Sheets("PlantAnalysis") 'find last row in column A LastRow = sh.range("A" & Rows.Count).End(xlUp).Row 'set range to loop thru Set MyRange = .range("A3:A" & LastRow) For Each rng In MyRange 'test if rng is empty, if so skip it If Not IsEmpty(.Cells(rng.Row, "A")) Then .cells(rng.row,"B3:B")= Application.vlookup(rng,'C:\[ItemMaster.xls List'!"$A:$C",3,FALSE) End If Next range End With End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data in external workbook
And
..cells(rng.row,"B3:B") would be more like: ..cells(rng.row,"B") (I didn't look at the rest of your code.) Dave Peterson wrote: First, the table file in your =vlookup() formula has to be open if the code is going to work. And then you'd use something like: .cells(rng.row,"B3:B")= Application.vlookup(rng, _ workbooks("ItemMaster.xls").worksheets("list") _ .range("A:C"), 3, false) if the itemaster.xls workbook won't be open, then I'd just plop the formula into the cell (and convert it to values????). Ray wrote: Hello: I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description Dim MyRange As range Dim LastRow As Long Dim rng As range With Sheets("PlantAnalysis") 'find last row in column A LastRow = sh.range("A" & Rows.Count).End(xlUp).Row 'set range to loop thru Set MyRange = .range("A3:A" & LastRow) For Each rng In MyRange 'test if rng is empty, if so skip it If Not IsEmpty(.Cells(rng.Row, "A")) Then .cells(rng.row,"B3:B")= Application.vlookup(rng,'C:\[ItemMaster.xls List'!"$A:$C",3,FALSE) End If Next range End With End Sub -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Data in external workbook
Thanks Dave,
I was able to sovlve wthout having to open the workbook "Dave Peterson" wrote: And ..cells(rng.row,"B3:B") would be more like: ..cells(rng.row,"B") (I didn't look at the rest of your code.) Dave Peterson wrote: First, the table file in your =vlookup() formula has to be open if the code is going to work. And then you'd use something like: .cells(rng.row,"B3:B")= Application.vlookup(rng, _ workbooks("ItemMaster.xls").worksheets("list") _ .range("A:C"), 3, false) if the itemaster.xls workbook won't be open, then I'd just plop the formula into the cell (and convert it to values????). Ray wrote: Hello: I have a worksheet called "PlantAnalysis". Column A has the "Item ID" and I would like to put the description in column B; starting at cell B3. I am trying to lookup the Item Description from an external workbook called "Item master" on worksheet labelled "List". Column A has the "Item ID" and Column C has the "Description".Is VLookup the recommended way to do this? Below is what I have but can't get it to work. Sub getdesc() 'Inserting vlookup statement to populate material description Dim MyRange As range Dim LastRow As Long Dim rng As range With Sheets("PlantAnalysis") 'find last row in column A LastRow = sh.range("A" & Rows.Count).End(xlUp).Row 'set range to loop thru Set MyRange = .range("A3:A" & LastRow) For Each rng In MyRange 'test if rng is empty, if so skip it If Not IsEmpty(.Cells(rng.Row, "A")) Then .cells(rng.row,"B3:B")= Application.vlookup(rng,'C:\[ItemMaster.xls List'!"$A:$C",3,FALSE) End If Next range End With End Sub -- Dave Peterson -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup changing data range in external workbook | Excel Worksheet Functions | |||
How do i reference an external workbook for data validation? | Excel Discussion (Misc queries) | |||
Reference Data in External Workbook | Excel Worksheet Functions | |||
Lookup in data from external datasource | Excel Worksheet Functions | |||
Lookup external data by cell or range of cells | Excel Programming |