Ufdjrw

I have a master price worksheet (Test Price) with product name (col A) and price (col B). I want to create a macro that when you click a button it will update the prices through the entire workbook. The previous person in my position already created a MOD that will update prices throughout the WB if it is changed in one WS. I am trying to link the master list to that code. So loop through the list and update one sheet which will use the existing mod to update all other sheets. Can anyone please help with this?

This is the code that updates the sheets, I need to link the master price list to this:

Sub ChangePrice(row As String, price As String)

 Dim cropVal As String: cropVal = Cells(row, 2).Value ' inefficient
 Dim LastRow As Long
 For Each ws In ActiveWorkbook.Worksheets

 'simple check for division in A3 (stronger check may be needed)
 If ws.Cells(3, 1).Value = "Division:" Then

 LastRow = ws.Range("A" & Rows.count).End(xlUp).row

 ' starts in row 12, though data starts in 13
 For i = 12 To LastRow

 'check column 2 if crop is the same
 If ws.Cells(i, 2).Value = cropVal Then

 'if so, change its price in column 10
 ws.Cells(i, 10).Value = price

 'this handles situations where the symbol is attached
 ElseIf ws.Cells(i, 2).Value = cropVal & "®" Then

 ws.Cells(i, 10).Value = price

 End If


 Next i


 End If
 Next ws

End Sub

You could create a dictionary of the values and then pass the dictionary to the module. You would need to add a For Each loop to your master sheet to find the row with the product for each specific worksheet.

Sub CropValFind()
Dim ProdCol As Range, Cell As Range, PriceCol As Range
Set ProdCol = 'Your product column range here
Set PriceCol = 'Your Price Column range here
For Each Cell in ProdCol
 Call ChangePrice(Cell.Value, CreateDictFromColumns("MasterSheetName", ProdCol.Column, PriceCol.Column))
Next
End Sub

Assuming your product and price columns are adjacent to each other and the values are strings:

Pulled from https://stackoverflow.com/a/33523909/10462532

Function CreateDictFromColumns(sheet As String, keyCol As String, valCol As String) As Dictionary
 Set CreateDictFromColumns = New Dictionary
 Dim rng As Range: Set rng = Sheets(sheet).Range(keyCol & ":" & valCol)
 Dim i As Long
 Dim lastCol As Long '// for non-adjacent ("A:ZZ")
 lastCol = rng.Columns.Count
 For i = 1 To rng.Rows.Count
 If (rng(i, 1).Value = "") Then Exit Function
 CreateDictFromColumns.Add rng(i, 1).Value, rng(i, lastCol).Value
 Next
End Function

Then your ChangePrice Sub would look something like this.

Sub ChangePrice(row As String, price As Dictionary)
Dim cropVal As String: cropVal = row 
Dim LastRow As Long
For Each ws In ActiveWorkbook.Worksheets

'simple check for division in A3 (stronger check may be needed)
If ws.Cells(3, 1).Value = "Division:" Then

 LastRow = ws.Range("A" & Rows.count).End(xlUp).row

 ' starts in row 12, though data starts in 13
 For i = 12 To LastRow

 'check column 2 if crop is the same
 If ws.Cells(i, 2).Value = cropVal Then

 'if so, change its price in column 10
 ws.Cells(i, 10).Value = price(row)

 'this handles situations where the symbol is attached
 ElseIf ws.Cells(i, 2).Value = cropVal & "®" Then

 ws.Cells(i, 10).Value = price(row)

 End If


 Next i


End If
Next ws
End Sub

A great resource to learn the in's and outs of dictionaries can be found here.