Hello,
I have a rather large spreadsheet named "Master" ( about 244k rows) of pricing information for a customer. Each row has a part number, brand name, pricing category, cost, margin, and price column. As it goes, it's time for annual price increases from vendors and I'm trying automate the update. I have a second sheet named "Sheet1" that has just the values that need updating. Basically this is supposed to go line by line on Sheet1, filter that part number on Master, then change the price cell value. It all works just fine as long as there are no duplicates. Unfortunately there are duplicates. I'm hoping you fine folk can help me figure out how to only worry about the first line. The error is happening on the line " comparePart = Sheets("Master").Range("B2:B" & LR).SpecialCells(xlCellTypeVisible) ", and only because, I assume, it's trying to grab a string value to compare but is getting multiple issues. Any suggestion? Thank you!!
I have a rather large spreadsheet named "Master" ( about 244k rows) of pricing information for a customer. Each row has a part number, brand name, pricing category, cost, margin, and price column. As it goes, it's time for annual price increases from vendors and I'm trying automate the update. I have a second sheet named "Sheet1" that has just the values that need updating. Basically this is supposed to go line by line on Sheet1, filter that part number on Master, then change the price cell value. It all works just fine as long as there are no duplicates. Unfortunately there are duplicates. I'm hoping you fine folk can help me figure out how to only worry about the first line. The error is happening on the line " comparePart = Sheets("Master").Range("B2:B" & LR).SpecialCells(xlCellTypeVisible) ", and only because, I assume, it's trying to grab a string value to compare but is getting multiple issues. Any suggestion? Thank you!!
VBA Code:
Sub UpdateCosts()
'Application.ScreenUpdating = False
ClearImportTableFilters
Dim comparePart As String
Dim importPart As String
Dim i As Long
Dim LR As Long
LR = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Sheets("Sheet1").Rows.Count
importPart = Sheets("Sheet1").Cells(i, 2)
Sheets("Master").Range("TableParts[Item Id]").AutoFilter Field:=2, Criteria1 _
:=importPart
comparePart = Sheets("Master").Range("B2:B" & LR).SpecialCells(xlCellTypeVisible)
If comparePart = importPart Then
Sheets("Master").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2).Select
End If
ClearMasterTableFilters
Sheets("Margins").Range("H9") = i
Next i
'Application.ScreenUpdating = True
End Sub