Forum Discussion

maxtreme01's avatar
maxtreme01
Copper Contributor
Jan 21, 2022

Price Increase from my supplier

Hi,

I have a spreadsheet with my product part numbers in one column and prices in the other, I have been sent a spreadsheet with price increases in the same format (part numbers in one column, prices in the other) by my supplier, Is there a way to match part numbers from the new spreadsheet to my spreadsheet part numbers with the update prices to transfer across?

 

On my data sheet is lots of other info as I import this into my website

 

regards

Liam

excel

3 Replies

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 21, 2022

        maxtreme01 

        Here is a macro you can run:

        Sub ReplacePrices()
    ' Assumption: part numbers in column A, starting in row 2.
    ' Prices in column P.
    Dim wss As Worksheet
    Dim wst As Worksheet
    Dim m As Long
    Dim rgs As Range
    Dim rgt As Range
    Application.ScreenUpdating = False
    ' Change the following two lines as needed
    Set wss = Workbooks("New.xlsx").Worksheets("New Prices")
    Set wst = Workbooks("Old.xlsx").Worksheets("Current Prices")
    m = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
    For Each rgs In wss.Range("A2:A" & m)
        Set rgt = wst.Range("A:A").Find(What:=rgs.Value, LookAt:=xlWhole, MatchCase:=False)
        If Not rgt Is Nothing Then
            wst.Range("P" & rgt.Row).Value = wss.Range("P" & rgs.Row).Value
        End If
    Next rgs
    Application.ScreenUpdating = True
End Sub

Resources