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("D3100"), 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
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("D3100"), 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