Search and Match from a range + Loop + VBA

The Student

New Member
Joined
Sep 27, 2018
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • LIST4.PNG
    LIST4.PNG
    12.9 KB · Views: 24

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yes that´s correct.
OK, give this version a try.

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
Cheers - glad to help. :)

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

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top