lostinvb
03-14-2019, 01:34 PM
I would like to open an invoice and use the macro to match an item# in column A to the wbPrice wsItems column A
The following code does all this among the same workbook but I would rather have it look externally.
"wsInvoice" instead will just be the active sheet and would search wbPrice-wsItems with the path C:\FileServer\CompanyDocs\My Excel\Price.xls
please help
Private Sub CommandButton1_Click()
Dim wsItems As Worksheet, wsInvoice As Worksheet '??????
Dim iInvoice As Long, iLastInvoice As Long
Dim iPrice As Long
Application.ScreenUpdating = False
Set wsItems = Worksheets("Items") ' path would be: C:\FileServer\CompanyDocs\My Excel\Price.xls
Set wsInvoice = Worksheets("Invoice") ' this would be obsolete ... would just be referencing active sheet
iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
For iInvoice = 1 To iLastInvoice
iPrice = 0
On Error Resume Next ' in case not found
iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
On Error GoTo 0
If iPrice > 0 Then
wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
Else
wsInvoice.Cells(iInvoice, 8).Value = ""
End If
Next iInvoice
Application.ScreenUpdating = True
End Sub
The following code does all this among the same workbook but I would rather have it look externally.
"wsInvoice" instead will just be the active sheet and would search wbPrice-wsItems with the path C:\FileServer\CompanyDocs\My Excel\Price.xls
please help
Private Sub CommandButton1_Click()
Dim wsItems As Worksheet, wsInvoice As Worksheet '??????
Dim iInvoice As Long, iLastInvoice As Long
Dim iPrice As Long
Application.ScreenUpdating = False
Set wsItems = Worksheets("Items") ' path would be: C:\FileServer\CompanyDocs\My Excel\Price.xls
Set wsInvoice = Worksheets("Invoice") ' this would be obsolete ... would just be referencing active sheet
iLastInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, 1).End(xlUp).Row
For iInvoice = 1 To iLastInvoice
iPrice = 0
On Error Resume Next ' in case not found
iPrice = Application.WorksheetFunction.Match(wsInvoice.Cells(iInvoice, 1).Value, wsItems.Columns(1), 0)
On Error GoTo 0
If iPrice > 0 Then
wsInvoice.Cells(iInvoice, 8).Value = wsItems.Cells(iPrice, 8).Value
Else
wsInvoice.Cells(iInvoice, 8).Value = ""
End If
Next iInvoice
Application.ScreenUpdating = True
End Sub