Help! ... need to vlookup but can't make it work!

R

Ray

Hi -

I've got two workbooks (ITEMMOVE.xls and my basebook). The Basebook
has a list of categories (B34:B48), I need to find these categories in
ITEMMOVE and return their values (4 columns over) back to the
Basebook. For categories that don't exist in ITEMMOVE, a zero should
be returned. Ddoes that make sense? basically, a vlookup -- seems
easy but I can't get it to work....

Can you help?
TIA,
Ray


Here's my code:
Private Sub UpdateInfo_Click()
Dim ITEMMOVE As String, Store As String, ACCT As String
Dim mybook As Workbook, basebook As Workbook
Dim myR As Range, x as String

If IsError(ThisWorkbook.Sheets("Date").Range("D1").Value) Then
MsgBox ("You must select a store ....")
Exit Sub
Else
End If

Store = Format(ThisWorkbook.Sheets("Date").Range("D1").Value,
"000")

ITEMMOVE = "\\blah\blahblah\blahblahblah\ITEMMOVE.xls"


Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set basebook = ThisWorkbook

Set mybook = Workbooks.Open(ITEMMOVE)

' Update ITEMMove info from CashRecs

basebook.Sheets("Tax Exempt").Activate
For Each cell In Range("C35:C48")
Art = cell.Offset(0, -1).Value
On Error Resume Next
x = Application.WorksheetFunction.VLookup(Art,
mybook.Sheets("Item Movement").Range("D3:D100"), 4, False)
cell.Value = x
On Error GoTo 0

Next cell
mybook.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = True

End Sub
 
J

Joel

You can use the find method

with mybook.Sheets("Item Movement")
set c = .Range("D3:D100").find(what:=art,lookin:=xlvalues)
if not c is nothing
x = c.offset(0,3).value
end if
end with
 
T

Tom Ogilvy

x
x = Application.WorksheetFunction.VLookup(Art,
mybook.Sheets("Item Movement").Range("D3:G100"), 4, False)

the lookup range must be at least 4 columns wide


As adjusted above it looks for the categories in column D and returns vaues
from F. Make adjustments as appropriate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top