Macro that updates my prices if there is a new price

[tonywatsonhelp]

Hi Everyone,

Hope you can help

I have two sheets

Event
Stock

Now in Event I have a list of stock items with prices
I'd like to be able to update these prices with the prices in my Stock tab,

So Event Range(N24:N & lastRow) is Name
So Event Range(P24:P & lastRow) is Price

So Stock Range(I11:I & lastRow) is Name
So Stock Range(J11:J & lastRow) is Price

so the macro in english would read something like,
For each Name in range Event Range(N24:N & lastRow) find name in Stock Range(I11:I & lastRow)
if you can not find this name, goto next,

if you find name then take the price from column J and put it into Event Range(P what ever row we are on.

hope thats clear,
please help if you can
Thanks
Tony

 

[offthelip]

try this which uses dictionary object and should be very fast:

Sub dictionary()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("Stock")
   lastrow = 200 ' I don't know how you are setting this
      Ary = .Range(.Cells(11, 9), .Cells(lastrow, 10))
   End With
   For i = 1 To UBound(Ary)
     If Ary(i, 1) <> "" Then
      Dic(Ary(i, 1)) = Ary(i, 2)    ' set price
     End If
   Next i
   With Worksheets("Events")
      Anm = .Range(.Cells(24, 14), .Cells(lastrow, 14))
      Aout = .Range(.Cells(24, 16), .Cells(lastrow, 16))
        For i = 1 To UBound(Anm)
          If (Dic.Exists(Anm(i, 1))) Then
            Aout(i, 1) = Dic.Item(Anm(i, 1))
          End If
        Next i
      .Range(.Cells(24, 16), .Cells(lastrow, 16)) = Aout
     
 End With
End Sub

 

[tonywatsonhelp]

Thanks off the lip this looks amazing