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.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

About how many products do you actually have in columns B:C?

 

[The Student]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

About how many products do you actually have in columns B:C?

Thanks for the info. Its updated now

The number of products varies. some are continuously replaced, removed and added. At the moment I believe we got around 250-300 products in our Brochure.

 

[Peter_SSs]

Thanks for the info. Its updated now

Thanks for that.
Next thing is to consider XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I believe we got around 250-300 products in our Brochure.

OK, thanks. Try this with a copy of your workbook.

Sub UpdatePrices()
  Dim a As Variant
  Dim rFound As Range
  Dim i As Long
  
  a = Range("B4", Range("C" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  With Range("F2:G" & Range("F" & Rows.Count).End(xlUp).Row)
    .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
        rFound.Offset(4).Value = a(i, 2)
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[The Student]

Thanks for that.
Next thing is to consider XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.


OK, thanks. Try this with a copy of your workbook.

Sub UpdatePrices()
  Dim a As Variant
  Dim rFound As Range
  Dim i As Long
 
  a = Range("B4", Range("C" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
  With Range("F2:G" & Range("F" & Rows.Count).End(xlUp).Row)
    .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
        rFound.Offset(4).Value = a(i, 2)
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

Hi Peter.
Thanks a lot. This is really helpful. ?
It works, Amazing!!

I will try to use the same steps.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Alex Blakenburg]

3. Update price in the Brochure based on Pricelist and colour it green

@Peter_SSs, I did not realise that find only looked at the visible rows and that applying a filter first might increase the speed of the Finds. Thank you.

@The Student, in case you still wanted the green, I have added a couple of lines to Peter's code, firstly to clear any previous colouring from Column G and then to apply the colour to the changed prices per you item 3. (there are various flavours of green, I have just used vbGreen numeric value 65280)

Sub UpdatePrices_Peter_SSs_mod()
  Dim a As Variant
  Dim rFound As Range
  Dim i As Long
  Dim rng As Range
 
  a = Range("B4", Range("C" & Rows.Count).End(xlUp)).Value
  Application.ScreenUpdating = False
 
  Set rng = Range("F2:G" & Range("F" & Rows.Count).End(xlUp).Row)
  ' Reset previous colouring
  rng.Offset(, 1).Resize(, 1).Interior.Color = xlNone
 
  With rng
    .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
        rFound.Offset(4).Value = a(i, 2)
        ' Apply colour to changed prices
        rFound.Offset(4).Interior.Color = vbGreen
      End If
    Next i
    .Parent.AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

@Peter_SSs, I did not realise that find only looked at the visible rows and that applying a filter first might increase the speed of the Finds.

I don't know if it would have any impact on the speed of the "Find" process and in any case I certainly did not do the AutoFilter for that reason. The reason was simply to ensure the correct cell was found when looking for the product number. For example, if the filter was not applied and in the OP's example if the new price of product 2000 was $5000 then the green cell in G8 would hold 5000 after the first loop. Then, when searching for the next product number (5000) the Find would find G8 not the correct cell G11 and therefore the incorrect cell would get updated.

Thanks for updating about the green - I had forgotten about that.

 

[The Student]

Hi @Peter_SSs & @Alex Blakenburg
I found out that some of our products appear more than more once in our Brochure.

Is there a way to work around this?

So they also will change in price and color.
Now they only change for the first appearance.

 

[Peter_SSs]

I found out that some of our products appear more than more once in our Brochure.

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