Lookup Data in external workbook

[Ray]

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]

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????).

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]

And

..cells(rng.row,"B3:B")
would be more like:

..cells(rng.row,"B")

(I didn't look at the rest of your code.)

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????).

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

 

[Ray]

Thanks Dave,
I was able to sovlve wthout having to open the 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.)

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????).

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