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").Va lue) Then
MsgBox ("You must select a store ....")
Exit Sub
Else
End If

Store = Format(ThisWorkbook.Sheets("Date").Range("D1").Val ue,
"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

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



"Ray" wrote:

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").Va lue) Then
MsgBox ("You must select a store ....")
Exit Sub
Else
End If

Store = Format(ThisWorkbook.Sheets("Date").Range("D1").Val ue,
"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

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.
--
Regards,
Tom Ogilvy



"Ray" wrote:

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").Va lue) Then
MsgBox ("You must select a store ....")
Exit Sub
Else
End If

Store = Format(ThisWorkbook.Sheets("Date").Range("D1").Val ue,
"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