************************************************************** Debug Stuffs F8 Steps into code test = SplitOriginalSku(0) If Not IsNull(SplitOriginalSku(1)) Then test2 = SplitOriginalSku(1) End If ************************************************************** Super loopy stuffs: As of 7/28/2016 this is my most painful project yet!!! VBA SUCKS!!! Sub createConfSkus() Dim C As Range Dim OriginalSku As String Dim ws1 As Worksheet Dim wb As Workbook Dim search As Range Dim LastRow As Long Dim aSku As String Dim bSku As String Dim cSku As String Dim dSku As String Dim eSku As String Set wb = Workbooks("SkuChanges.xlsm") Set ws1 = wb.Worksheets("Skus_To_Change") Set ws2 = wb.Worksheets("template") Set ws3 = wb.Worksheets("All_Products") For Each C In ws1.Range("A1", ws1.Range("A" & Rows.count).End(xlUp)) OriginalSku = C 'Grab the current cell SkuExists = "" 'Set empty until triggered ComboRan = False 'Set false until triggered SetVariables = False 'Set false until triggered aSku = "" 'Set empty until filled bSku = "" 'Set empty until filled cSku = "" 'Set empty until filled dSku = "" 'Set empty until filled eSku = "" 'Set empty until filled 'Before we begin let's split the sku's apart If InStr(OriginalSku, "/") Then 'if it see's a / change the command to account for double sku's VarSplits = Split(OriginalSku, "/") 'Empty Split() into a variable VarSplits Else VarSplits = Split(OriginalSku, " ") 'Empty Split() into a variable VarSplits End If 'get the Length of VarSplits to check for 2 D's later on... ArrayLen = Application.WorksheetFunction.CountA(VarSplits) 'Next we fill the 'REAL' array with sku stuff and add the A1's and A2's etc... Dim SplitOriginalSku() 'add an empty array Dim i As Integer i = 0 For Each Splits In VarSplits 'add all the VarSplits to the "array" 'First add the X0 to the array ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = Splits 'add each split to the end of the array i = i + 1 'increment i 'then add each possible color: If InStr(Splits, "A0") Then 'Add Matching A1 and A2 a1 = "A1" & Right(Splits, 3) a2 = "A2" & Right(Splits, 3) ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = a1 'add the A1 Sku i = i + 1 'increment i ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = a2 'add the A2 Sku i = i + 1 'increment i ElseIf InStr(Splits, "B0") Then 'Add Matching B1 and B2 b1 = "B1" & Right(Splits, 3) b2 = "B2" & Right(Splits, 3) ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = b1 'add the B1 Sku i = i + 1 'increment i ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = b2 'add the B2 Sku i = i + 1 'increment i ElseIf InStr(Splits, "C0") Then 'Add Matching C1 and C2 c1 = "C1" & Right(Splits, 3) c2 = "C2" & Right(Splits, 3) ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = c1 'add the C1 Sku i = i + 1 'increment i ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = c2 'add the C2 Sku i = i + 1 'increment i ElseIf InStr(Splits, "D0") Then 'Add Matching D1 and D2 If InStr(Splits, "////") Then 'This just doesn't even work at the moment but who cares... d0 = Splits d1 = "D1" & Right(Splits, 3) d2 = "D2" & Right(Splits, 3) ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = d1 'add the D1 Sku i = i + 1 'increment i ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = d2 'add the D2 Sku i = i + 1 'increment i End If ElseIf InStr(Splits, "E0") Then 'Add Matching E1 and E2 e1 = "E1" & Right(Splits, 3) e2 = "E2" & Right(Splits, 3) ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = e1 'add the E1 Sku i = i + 1 'increment i ReDim Preserve SplitOriginalSku(i) SplitOriginalSku(UBound(SplitOriginalSku)) = e2 'add the E2 Sku i = i + 1 'increment i End If Next Splits 'Start processing individual sku's For Each element In SplitOriginalSku 'First let's make sure we carry this one by looking in the product list.... Sheets("All_Products").Select '***********switch this back to All_Products after testing************** Columns("A:A").Select Set search = Selection.Find(What:=(element), After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False) 'Before we do logic craziness, let's make a variable for each element with the letter for that element a/b/c/etc just so it's easier to work with 'it's a little repetitive, but it has to get new variables each time the array is filled so... If SetVariables = False Then For Each q In SplitOriginalSku If InStr((q), "A0") = 1 Then aSku = q ElseIf InStr((q), "B0") = 1 Then bSku = q ElseIf InStr((q), "C0") = 1 Then cSku = q ElseIf InStr((q), "D0") = 1 Then dSku = q ElseIf InStr((q), "E0") = 1 Then eSku = q ElseIf InStr((q), "A1") = 1 Then a1Sku = q ElseIf InStr((q), "B1") = 1 Then b1Sku = q ElseIf InStr((q), "C1") = 1 Then c1Sku = q ElseIf InStr((q), "D1") = 1 Then d1Sku = q ElseIf InStr((q), "E1") = 1 Then e1Sku = q ElseIf InStr((q), "A2") = 1 Then a2Sku = q ElseIf InStr((q), "B2") = 1 Then b2Sku = q ElseIf InStr((q), "C2") = 1 Then c2Sku = q ElseIf InStr((q), "D2") = 1 Then d2Sku = q ElseIf InStr((q), "E2") = 1 Then e2Sku = q End If Next q End If ' End of setvariables SetVariables = True If search Is Nothing Then 'if the search finds any part of the origian sku, set a flag: SkuExists = False 'skip this sku LastRow = ws2.UsedRange.Rows.count 'find the last row in the template NewRow = LastRow + 1 'increment the last row by 1 ws2.Cells(NewRow, 6).Value = OriginalSku 'Always add the original sku to column f ws2.Cells(NewRow, 7).Value = (element) & "**Not Found**" 'Always add the single sku element to column G Else SkuExists = True LastRow = ws2.UsedRange.Rows.count 'find the last row in the template NewRow = LastRow + 1 'increment the last row by 1 ws2.Cells(NewRow, 6).Value = OriginalSku 'Always add the original sku to column f ws2.Cells(NewRow, 7).Value = (element) 'Always add the single sku element to column G If Not aSku = "" And Not bSku = "" And cSku = "" And dSku = "" And eSku = "" Then 'If it's a/b only: If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not cSku = "" And dSku = "" And eSku = "" Then 'If it's a/b/c only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not dSku = "" And ArrayLen = 3 And cSku = "" And eSku = "" Then 'If it's a/b/d only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & dSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & d1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & d2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not cSku = "" And Not dSku = "" And eSku = "" Then 'If it's a/b/c/d only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku & "/" & dSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku & "/" & d1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku & "/" & d2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & dSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & d1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & d2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not cSku = "" And dSku = "" And Not eSku = "" Then 'If it's a/b/c/e only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku & "/" & eSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku & "/" & e1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku & "/" & e2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & eSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & e1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & e2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not cSku = "" And Not dSku = "" And Not eSku = "" Then 'if it's all of them a/b/c/d/e If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku & "/" & dSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku & "/" & d1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku & "/" & d2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & cSku & "/" & eSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & c1Sku & "/" & e1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & c2Sku & "/" & e2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & dSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & d1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & d2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & eSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & e1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & e2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And cSku = "" And dSku = "" And Not eSku = "" Then 'If it's a/b/e only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & eSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & e1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & e2Sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And Not dSku = "" And cSku = "" And eSku = "" Then 'If it's a/b/d/d only If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & VarSplits(2) ' this will allow it to get the First D in the sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & ("D1" & Right(VarSplits(2), 3)) ' this will allow it to get the First D in the sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & ("D2" & Right(VarSplits(2), 3)) ' this will allow it to get the First D in the sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & VarSplits(3) ' this will allow it to get the second D in the sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & ("D1" & Right(VarSplits(3), 3)) ' this will allow it to get the second D in the sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & ("D2" & Right(VarSplits(3), 3)) ' this will allow it to get the second D in the sku ComboRan = True End If ElseIf Not aSku = "" And Not bSku = "" And cSku = "" And Not dSku = "" And Not eSku = "" Then 'if it's a/b/d/e If ComboRan = False Then NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & dSku 'can never have a/b/ with d/e together NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & d1Sku 'can never have a/b/ with d/e together NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & d2Sku 'can never have a/b/ with d/e together NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = aSku & "/" & bSku & "/" & eSku 'can never have a/b/ with d/e together NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a1Sku & "/" & b1Sku & "/" & e1Sku 'can never have a/b/ with d/e together NewRow = NewRow + 1 ' increment the rows again ws2.Cells(NewRow, 6).Value = OriginalSku ws2.Cells(NewRow, 7).Value = a2Sku & "/" & b2Sku & "/" & e2Sku 'can never have a/b/ with d/e together ComboRan = True End If End If ' End of combo elseif's LastRow = 0 NewRow = 0 End If ' End of 'Not search Is Nothing Then' Next element ' For Each element In SplitOriginalSku Next C ' For Each Cell in WB 'unused code: 'get the array Length the first time to add the colors 'ArrayLen = Application.WorksheetFunction.CountA(SplitOriginalSku) 'Add all of the colors for each SplitOriginalSku 'For Each SkuColor In SplitOriginalSku 'If InStr(SkuColor, "A0") Then 'Add Matching A1 and A2 'If UBound(SplitOriginalSku) > 1 Then 'ReDim Preserve SplitOriginalSku(0 To ArrayLen) 'ReDim Preserve SplitOriginalSku(LBound(SplitOriginalSku) To UBound(SplitOriginalSku) + 1) 'End If ' myArr(UBound(myArr)) = ArrayLen 'Declare a single-dimension array of 5 values Dim numbers(4) As Integer 'ReDim Preserve = Redefine the size of an existing array retaining the current values-- ReDim Preserve numbers(15) 'ReDim SplitOriginalSku(ArrayLen To UBound(SplitOriginalSku) + 1) As String ' add value on the end of the array 'SplitOriginalSku(UBound(SplitOriginalSku)) = Value ' ElseIf InStr(SkuColor, "B0") Then 'Add Matching B1 and B2 ' ElseIf InStr(SkuColor, "C0") Then 'Add Matching C1 and C2 ' ElseIf InStr(SkuColor, "D0") Then 'Add Matching D1 and D2 ' ElseIf InStr(SkuColor, "E0") Then 'Add Matching E1 and E2 ' End If ' Next SkuColor 'SplitOriginalSku = Split(OriginalSku, "/") Original--was working but SplitOriginalSku was a "variable" and could not be ReDim'd 'Combo1Ran = False 'Set false until triggered 'Combo2Ran = False 'Set false until triggered 'Combo3Ran = False 'Set false until triggered 'Combo4Ran = False 'Set false until triggered 'Combo5Ran = False 'Set false until triggered 'Combo6Ran = False 'Set false until triggered 'Combo7Ran = False 'Set false until triggered 'Combo8Ran = False 'Set false until triggered 'Combo9Ran = False 'Set false until triggered ' For Each q In SplitOriginalSku ' If InStr((q), "A") = 1 Then ' aSku = q ' ElseIf InStr((q), "B") = 1 Then ' bSku = q ' ElseIf InStr((q), "C") = 1 Then ' cSku = q ' ElseIf InStr((q), "D") = 1 Then ' dSku = q ' ElseIf InStr((q), "E") = 1 Then ' eSku = q ' End If ' Next q End Sub ---------------------------- elements are a/b/c/d/e for each element in array Send to VarGen Case instr(element), A A = array(element #) InStr(OriginalSku, "/") end for each ---------------------------- //This compares one string with another X = InStr("start position", string1, string2, CompareMethod.Text) ---------------------------- skuClass = C 'gets SKU Classification strArray = Split(skuClass, " ") a = strArray(0) b = strArray(1) spacePosition = InStr(skuClass, " ") If skuClass = "Cargo Liner" Then C.Offset(0, 8) = "Cargo Liner" ElseIf skuClass = "Bundle Set" Then C.Offset(0, 8) = "Floor Mats / Cargo Liner" Else skuFirst = Left(skuClass, spacePosition - 1) lenghtSku = Len(skuClass) skuAfter = Right(skuClass, Len(skuClass) - spacePosition) C.Offset(0, 8) = "Floor Mats" End If --------------------------------- Private Sub CommandButton1_Click() Range("A1").Value = "Boo" //to change s different worksheet include the Worksheet object: Worksheets("Sales").Range("A1").Value = "Boo" //Sheet3.Range("A10").Value = "Boo" (never changes even if name of worksheet changes, listed in explorer under objects) MsgBox "Entered value is " & Range("A1").Value Worksheets.Add MsgBox "Total worksheets is now: " & Worksheets.Count Cells(8, 2).Value = 2 Range("A1:A5,B3:C4").Value = 10 Set example = Range("d1:e4") example.Value = "boo" example.Select example.Rows(4).Select example.Columns(2).Select ActiveSheet.Paste Range("C3:C4").ClearContents Range("C3:C4").ClearFormats MsgBox example.Rows.Count Dim continue As Boolean Dim y As Double Dim book As String Dim x As Integer x = 6 y = 5.5 book = "How to Code" continue = True Range("A1").Value = x Range("A2").Value = book MsgBox "value is " & y (displays 5.5) If continue = True Then do stuff Dim score As Integer, result As String score = Range("A1").Value If score >= 60 Then result = "pass" Else result = "fail" End If Range("B1").Value = result Dim c As Integer, i As Integer, j As Integer For c = 1 To 3 For i = 1 To 6 For j = 1 To 2 Sheet2.Cells(i, j).Value = 50 Next j Next i Next c //as long as Cells(i, 1).Value is not empty (<> means not equal to), Excel VBA enters the value into the cell at the intersection of row i and column 2, that is 10 higher than the value in the cell at the intersection of row i and column 1. Excel VBA stops when i equals 7 because Cells(7, 1).Value is empty Dim i As Integer i = 1 Do While Cells(i, 1).Value <> "" Cells(i, 2).Value = Cells(i, 1).Value + 10 i = i + 1 Loop Dim text As String text = "example text" //To extract the leftmost characters from a string, use Left. MsgBox Left(text, 4) //To extract the rightmost characters from a string, use Right. MsgBox Right("example text", 2) //To extract a substring, starting in the middle of a string, use Mid. MsgBox Mid("example text", 9, 2) //To find the position of a substring in a string, use Instr. MsgBox Instr("example text", "am") //To get the length of a string, use Len. MsgBox Len("example text") Dim firstDate As Date, secondDate As Date //To add a number of days to a date firstDate = DateValue("Jun 19, 2010") secondDate = DateAdd("d", 3, firstDate) MsgBox secondDate // one-dimensional array Dim Films(1 To 5) As String Films(1) = "Lord of the Rings" Films(2) = "Speed" Films(3) = "Star Wars" Films(4) = "The Godfather" Films(5) = "Pulp Fiction" MsgBox Films(4) //two-dimensional array Dim Films(1 To 5, 1 To 2) As String //5 rows and 2 columns. rows go first, then columns Dim i As Integer, j As Integer For i = 1 To 5 For j = 1 To 2 Films(i, j) = Cells(i, j).Value Next j Next i MsgBox Films(4, 2) Function Area(x As Double, y As Double) As Double Area = x * y End Function //To stop displaying code while it's running: Application.ScreenUpdating = False For i = 1 To 10000 Range("A1").Value = i Next i Application.ScreenUpdating = True //To modify font: Selection.Font.Color = vbBlue /usr/lib/sftp-server -f local5 -l info End Sub ************************************************************** Sub assignCategory() Dim C As Range Dim skuClass As String Dim ws1 As Worksheet Set ws1 = Worksheets("Current_Products") For Each C In ws1.Range("N2", ws1.Range("N" & Rows.Count).End(xlUp)) skuClass = C 'gets SKU Classification If skuClass = "Cargo Liner" Then C.Offset(0, 8) = "Cargo Liner" ElseIf skuClass = "Bundle Set" Then C.Offset(0, 8) = "Floor Mats / Cargo Liner" Else C.Offset(0, 8) = "Floor Mats" End If Next C End Sub ************************************************************** Sub assignShipping() Dim C As Range Dim skuClass As String Dim ws1 As Worksheet Set ws1 = Worksheets("Current_Products") For Each C In ws1.Range("N2", ws1.Range("N" & Rows.Count).End(xlUp)) skuClass = C 'gets SKU Classification If skuClass = "Cargo Liner" Then C.Offset(0, -3) = 15 C.Offset(0, -2) = 45 ElseIf skuClass = "Bundle Set" Then C.Offset(0, -3) = 32 C.Offset(0, -2) = 90 Else C.Offset(0, -3) = 17 C.Offset(0, -2) = 45 End If Next C End Sub ************************************************************** Sub positionVehicle() Dim C As Range Dim skuClass As String Dim ws1 As Worksheet Set ws1 = Worksheets("Current_Products") For Each C In ws1.Range("N2", ws1.Range("N" & Rows.Count).End(xlUp)) skuClass = C 'gets SKU Classification If skuClass = "Cargo Liner" Then cCoverage = C.Offset(0, 2) C.Offset(0, 9) = "Cargo Area " & cCoverage ElseIf skuClass = "Bundle Set" Then fmCoverage = C.Offset(0, 1) cCoverage = C.Offset(0, 2) If fmCoverage = "2 Rows" Then C.Offset(0, 9) = "First Row, Second Row and Cargo Area " & cCoverage ElseIf fmCoverage = "3 Rows" Then C.Offset(0, 9) = "First Row, Second Row, Third Row and Cargo Area " & cCoverage End If ElseIf skuClass = "First Row" Or skuClass = "Second Row" Or skuClass = "Second and Third Row" Or skuClass = "Third Row" Then C.Offset(0, 9) = skuClass ElseIf skuClass = "2 Row Set" Then C.Offset(0, 9) = "First Row and Second Row" ElseIf skuClass = "3 Row Set" Then C.Offset(0, 9) = "First Row, Second Row and Third Row" End If Next C End Sub ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** ************************************************************** **************************************************************