lostinvb
04-11-2019, 04:13 PM
active worksheet has item numbers in column A
workbook "pricing" has the same item numbers in A and a price in H
(some of the item numbers are duplicated in "pricing" so I need the search for each item number to stop as soon as it is first found)
also the workbook "pricing" may be already opened by a different user.... so read only?
I need the macro to start in A1 of active worksheet and search column A in "pricing" for the item number if found copy H to H of the active sheet.
item number may not be found
The following code I don't prefer because it copies the final instance of price number.
It also isn't closing "pricing" after macro ran
I've seen vlookup or match but am unable to adapt code myself
Sub pricetheinvoice()
Dim cl As Range Dim dic As Object Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set ws2 = ThisWorkbook.ActiveSheet
Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Copy of Pricing.xls"
Set wb1 = ActiveWorkbook
Set ws1 = ActiveSheet
Set dic = CreateObject("scripting.dictionary")
For Each cl In ws1.Range("A1", ws1.Range("A" & Rows.Count).End(xlUp))
dic(cl.Value) = cl.Offset(, 7).Value
Next cl
For Each cl In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
cl.Offset(, 7).Value = dic(cl.Value)
Next cl
End Sub
workbook "pricing" has the same item numbers in A and a price in H
(some of the item numbers are duplicated in "pricing" so I need the search for each item number to stop as soon as it is first found)
also the workbook "pricing" may be already opened by a different user.... so read only?
I need the macro to start in A1 of active worksheet and search column A in "pricing" for the item number if found copy H to H of the active sheet.
item number may not be found
The following code I don't prefer because it copies the final instance of price number.
It also isn't closing "pricing" after macro ran
I've seen vlookup or match but am unable to adapt code myself
Sub pricetheinvoice()
Dim cl As Range Dim dic As Object Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set ws2 = ThisWorkbook.ActiveSheet
Workbooks.Open Filename:="C:\FileServer\CompanyDocs\My Excel\Copy of Pricing.xls"
Set wb1 = ActiveWorkbook
Set ws1 = ActiveSheet
Set dic = CreateObject("scripting.dictionary")
For Each cl In ws1.Range("A1", ws1.Range("A" & Rows.Count).End(xlUp))
dic(cl.Value) = cl.Offset(, 7).Value
Next cl
For Each cl In ws2.Range("A1", ws2.Range("A" & Rows.Count).End(xlUp))
cl.Offset(, 7).Value = dic(cl.Value)
Next cl
End Sub