Option Explicit '<~~ this forces you to declare all variables used by type
Option Compare Text '<~~ this make text comparisons non-case specific
Sub Zack123()
'** First section declares all variables
Dim wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim c As Range, rng As Range
Dim FileName As String
Dim x As Long, lr As Long
Application.ScreenUpdating = False '<~~ stops screen 'flickering' - speeds up code execution
'** Get the operator to select the correct csv file, filtered for csv files
FileName = Application.GetOpenFilename _
(filefilter:="Excel files (*.csv),*.csv", MultiSelect:=False)
'** Open the selected file & call it "wb2" for code purposes
Set wb2 = Workbooks.Open(FileName)
Set ws1 = ActiveSheet '<~~ designate the csv sheet1 as "ws1" for code purposes
'** Define what "rng" refers to - all the used cells in column K from K2 down
Set rng = ws1.Range("K2", Cells(Rows.Count, "K").End(xlUp)) '<~~ one of several ways to do this
'** Loop through each cell ("c") in the defined range - change its value if it matches one of the
'values specified. The asterisk * is a 'wildcard' means the string just needs to contain the
'value - anything can appear before or after it in the cell
For Each c In rng
If c Like "*BOX*" Or _
c Like "*BASE*" Or _
c Like "*RISER*" Or _
c Like "*COLLAR*" Then
If Right(c, 3) <> c.Offset(, 1) Then '<~~ this prevents 2 weights being entered
c = c & "," & c.Offset(, 1)
End If
End If
Next c
'** This adds a new sheet to the csv, calls it "Sheet2" initially, then "ws2" for coding purposes
'It will be used for all the 'grunt' work and deleted later
wb2.Sheets.Add(after:=wb2.Sheets(Sheets.Count)).Name = "Sheet2"
Set ws2 = wb2.Sheets("Sheet2")
'** Next line gets the last row with data ("lr") in column 1 ("A") in the Master workbook
'In code, refering to "Sheet1" this way always refers to sheet1 in the file that
'contains the code you are running. In this case, the Master file
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range("A1:B" & lr).Copy ws2.Range("G1") '<~~ copies from A1 to B(last row) from the Master to sheet2 of the csv
ws2.Range("G1:H" & lr).Name = "FindItem" '<~~ calls the copied range "FindItem"
'** This part's new - get the exceptions list from the Master & copy it to sheet2 of the csv
Sheet1.Range("exceptions").Copy ws2.Range("I2")
lr = ws1.Cells(Rows.Count, "K").End(xlUp).Row '<~~ get the last row of column K in sheet1 of the csv
ws1.Range("K2:L" & lr).Copy ws2.Range("A2") '<~~ copy columns L & K from sheet1 to sheet2 of the csv
Set rng = ws2.Range("A2:A" & lr) '<~~ redefine what "rng" refers to - better to reuse variable names
'** Loop through each cell ("c") in the defined range, getting the 'weight range' each item
'falls into
For Each c In rng
If c Like "*BOX*" Or _
c Like "*BASE*" Or _
c Like "*RISER*" Or _
c Like "*COLLAR*" Then
x = Application.WorksheetFunction.Floor(Val(c.Offset(, 1)), 12) '<~~ rounds down to nearest factor of 12
c = Left(c, Len(c) - 3) & CStr(x) & """" '<~~ changes the value in the cell
End If
Next
'** This part's new - get the item# and yes/no for use with exceptions
ws1.Range("D2:E" & lr).Copy ws2.Range("C2")
'** Use a Vlookup to return the item number for ALL items (initially) where they exist
'This may or may not be used depending on the exceptions
With ws2.Range("E2:E" & lr)
.Formula = "=IFERROR(Vlookup(A2,FindItem,2,false),"""")"
.Value = .Value
End With
'** This part's new - get whether the item is on the exceptions list
With ws2.Range("F2:F" & lr)
.Formula = "=IFERROR(Vlookup(A2,I$2:I$" & lr & ",1,false),""N"")"
.Value = .Value
End With
'** Do the final calculation to get the item# for pasting back to sheet1 of the csv
'** I expect it'll take several attempts to get the logic right
With ws2.Range("J2:J" & lr)
.FormulaR1C1 = "=IF(RC6<>""N"",IF(RC4=""Yes"",RC5,),IF(RC5<>"""",RC5,RC3))"
.Value2 = .Value2
For Each c In ws2.Range("J2:J" & lr)
If c = 0 Then c = ""
Next c
.Copy ws1.Range("D2")
End With
'** Switch off Excel's warnings to enable deletion of sheet2 of the csv
Application.DisplayAlerts = False
ws2.Delete '<~~ delete sheet2 of the csv - no longer needed
Application.DisplayAlerts = True '<~~ turn warnings back on - ALWAYS do this
End Sub
