Search and Match from a range + Loop + VBA

[The Student]

Hi
I´m sitting with a job-task I would like ideas on. I got two tables. See picture attached.

1. "Pricelist" with product-numbers and prices (Column B+C)
2. "Brochure" that contains same information. (F+G)

I need to update prices from my "Pricelist" to my "Brochure".

My idea:

1. Search for product no. "2000" in the "Brochure " (first product no. in the Pricelist - Cell B4)
2. Then offset(4,0) - The price will always be 4 rows below the product no. in the Brochure sheet.
3. Update price in the Brochure based on Pricelist and colour it green
4 Loop through the Pricelist and add a new price to every search it finds.

I´m not sure about the best approach to do this and hope someone can help.

 

[The Student]

.. but they only appear once in the Pricelist in columns B:C?

Yes that´s correct.

 

[Peter_SSs]

Yes that´s correct.

OK, give this version a try.

Sub UpdatePrices_v2()
  Dim a As Variant
  Dim rFound As Range
  Dim i As Long
  Dim FirstAddr As String
  
  a = Range("B4", Range("C" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  With Range("F2", Range("G" & Rows.Count).End(xlUp))
    .Columns(2).Interior.Color = xlNone
    .AutoFilter Field:=1, Criteria1:="Prod*"
    For i = 1 To UBound(a)
      Set rFound = .Columns(2).Find(What:=a(i, 1), LookAt:=xlWhole)
      If Not rFound Is Nothing Then
        FirstAddr = rFound.Address
        Do
          With rFound.Offset(4)
            .Value = a(i, 2)
            .Interior.Color = vbGreen
          End With
          Set rFound = .FindNext(rFound)
        Loop Until rFound.Address = FirstAddr
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[The Student]

Thanks @Peter_SSs

Works like a charm
Really appreciate your help. Going to make our work so much easier.
and good learning material as well.

 

[Peter_SSs]

Cheers - glad to help.

BTW, I have deleted your second last post since it was just a repeat of my previous one.