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 |
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 |
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 |
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 . |
All times are GMT +1. The time now is 04:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com