Master Price List update

[danturv]

Hi All

I have a master price list I can download in csv (appox 6000 lines) and I receive price updates from many suppliers in Excel. What would be the best way to to update prices where the product code matches between the two sheets? Sorry I am a bit excel rusty!

 

[mumps]

Try:

Sub Updateprices()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, arr() As Variant, dic As Object, cnt As Long: cnt = 0
    Set srcWS = Sheets("PriceUpdate")
    Set desWS = Sheets("MasterPrice")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        dic.Add v1(i, 1), v1(i, 2)
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("B" & i + 1) = dic(v2(i, 1))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[danturv]

Works like a dream, thank you so much!

 

[mumps]

You are very welcome.