Object or With Block Variable Not Set

[slimjm911]

Hey VbPros,

Is there a way to have an error occur more than once?

For example i have a chunk of code that looks for a value withing a range and if not found proceeds to handle the error. It is in a loop however and only does it once. Is there a way to make it happen everytime the loop runs through rather than just once. Does the computer remember that that particular line of code threw an error and throws an error back at you? Here is the code:

 On Error GoTo ErrHandler:
                     FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor).Row
                     If Val(FoundManufacturerColor) > 0 Then
                        Worksheets("sheet1").Range("S" & FirstLine).Value = Worksheets("sheet5").Range("CS" & FoundManufacturerColor)
                        GoTo MainColorBreak
                     End If

Here is the Error Handler Section:

ErrHandler:
If Not NewColor1 = NewColor Then
NewColor = InputBox("Manufacturer Color " & FormatManufacturerColor & " not found. Input New Main Color Here")
NewColor1 = NewColor
GoTo Continue:
Else
FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor).Row
GoTo Continue:
End If

I want the error handling section to process everytime the loop goes through and does not find FormatManufacturerColor. It does it once but on the second run throws the "Object or With Block Variable Not Set" error. Any suggestions?

Here is the entire code:

Sub Fomat()
Application.EnableCancelKey = xlDisabled

Dim Brand As String

Brand = InputBox("What Brand Is This For")
Worksheets("sheet1").Rows("2:2000").Delete

If Brand = "Puma" Then



Worksheets("sheet2").Range("A1").Value = "Puma"
Worksheets("sheet2").Range("A3").Value = "Style No."
Worksheets("sheet2").Range("B3").Value = "Model Name"
Worksheets("sheet2").Range("C3").Value = "Manufacturer Color"
Worksheets("sheet2").Range("D3").Value = "4"
Worksheets("sheet2").Range("E3").Value = "4.5"
Worksheets("sheet2").Range("F3").Value = "5"
Worksheets("sheet2").Range("G3").Value = "5.5"
Worksheets("sheet2").Range("H3").Value = "6"
Worksheets("sheet2").Range("I3").Value = "6.5"
Worksheets("sheet2").Range("J3").Value = "7"
Worksheets("sheet2").Range("K3").Value = "7.5"
Worksheets("sheet2").Range("L3").Value = "8"
Worksheets("sheet2").Range("M3").Value = "8.5"
Worksheets("sheet2").Range("N3").Value = "9"
Worksheets("sheet2").Range("O3").Value = "9.5"
Worksheets("sheet2").Range("P3").Value = "10"
Worksheets("sheet2").Range("Q3").Value = "10.5"
Worksheets("sheet2").Range("R3").Value = "11"
Worksheets("sheet2").Range("S3").Value = "11.5"
Worksheets("sheet2").Range("T3").Value = "12"
Worksheets("sheet2").Range("U3").Value = "13"
Worksheets("sheet2").Range("V3").Value = "14"
Worksheets("sheet2").Range("W3").Value = "15"
Worksheets("sheet2").Range("X3").Value = "Total Quantity"
Worksheets("sheet2").Range("Y3").Value = "Seller Cost"
Worksheets("sheet2").Range("Z3").Value = "Total Price"


Dim Sheet5 As Worksheet
Dim Cell As Range
Dim RangeR As Range
Dim Start As Integer
Dim FirstLine As Integer
Dim StartRow As String
Dim NextRow As Integer
Dim LastLine As Integer
Dim NewFirstLine As Integer
Dim SizeRow As Integer
Dim ColumnStart As Integer
Dim I As Integer
Dim Search As String
Dim ManufacturerColor As String
Dim Model As String
Dim ModelName As String
Dim FoundModelName As String
Dim FoundModel As String
Dim FormatManufacturerColor As String
Dim FoundManufacturerColor As String
Dim N As Integer
Dim NewColor As String

NewColor = "NewColor"
StartRow = InputBox("What row contains the first model?")
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Rows(StartRow).Select

SizeRow = 24
NewFirstLine = 2
RangeStartLine = 2
Start = 3
I = -1

Search = InputBox("What Are You Searching By? Model Name or Model Number?")
For StartRowNumber = Val(StartRow) To LastRow Step 1


    NumberofSizes = WorksheetFunction.CountA(Range("D" & StartRowNumber, "W" & StartRowNumber))
    LastLine = (NewFirstLine + NumberofSizes) + 1
    RangeLastLine = (NumberofSizes + RangeStartLine) + 1
    
                
                For Each Cell In Range("D" & StartRowNumber & ":W" & StartRowNumber)
                Cell = Application.WorksheetFunction.Trim(Cell)
                Cell = Application.WorksheetFunction.Clean(Cell)
                Next

                For Each RangeR In Range("D" & StartRowNumber & ":W" & StartRowNumber)
                     
                    If RangeR.Value > 0 Then
                        Worksheets("sheet1").Range("R" & Start).Value = RangeR.Offset(I).Value
                        Worksheets("sheet1").Range("D" & Start).Value = RangeR.Value
                        RangeStartLine = Start + 1
                        Start = RangeStartLine
                    If RangeStartLine = RangeLastLine Then
                        Worksheets("sheet1").Range("R" & RangeLastLine).Delete
                        Start = RangeLastLine + 1
                        I = I - 1
                    End If
                    End If
                Next RangeR

                For FirstLine = NewFirstLine To LastLine Step 1
                     Worksheets("sheet1").Range("Q" & FirstLine).Value = Range("A" & StartRowNumber)
                     Worksheets("sheet1").Range("P" & FirstLine).Value = Range("B" & StartRowNumber)
                     Worksheets("sheet1").Range("N" & FirstLine).Value = Range("C" & StartRowNumber)
                     
                       ManufacturerColor = Worksheets("Sheet1").Range("N" & FirstLine).Value
                     
                     If InStr(ManufacturerColor, "ROSSA") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "ROSSA", "ROSSO")
                     End If
                     If InStr(ManufacturerColor, "-WH") Then
                     If InStr(ManufacturerColor, "WHITE") Then GoTo Break
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-WH", "-WHITE")
                     End If
Break:
                     If InStr(ManufacturerColor, "-BL") Then
                     If Not InStr(ManufacturerColor, "BLUE") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-BL", "-BLUE")
                     Else: MsgBox ("dog1")
                     End If
                     End If
                     If InStr(ManufacturerColor, "-BLK") Then
                     If Not InStr(ManufacturerColor, "BLACK") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-BLK", "-BLACK")
                     Else: MsgBox ("dog2")
                     End If
                     End If
                     If InStr(ManufacturerColor, "-PWTR") Then
                     If Not InStr(ManufacturerColor, "PEWTER") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-PWTR", "-PEWTER")
                     Else: MsgBox ("dog3")
                     End If
                     End If
                     If InStr(ManufacturerColor, "-CO") Then
                     If Not InStr(ManufacturerColor, "CORSA") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-CO", "-CORSA")
                     Else: MsgBox ("dog4")
                     End If
                     End If
                     If InStr(ManufacturerColor, "-YELL") Then
                     If Not InStr(ManufacturerColor, "YELLOW") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-YELL", "-YELLOW")
                     Else: MsgBox ("dog5")
                     End If
                     End If
                     If InStr(ManufacturerColor, "-SHA") Then
                     If Not InStr(ManufacturerColor, "SHADOW") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-SHA", "-SHADOW")
                     Else: MsgBox ("dog6")
                     End If
                     End If
                     If InStr(ManufacturerColor, " W") Then
                     If Not InStr(ManufacturerColor, "WHITE") Then
                     ManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, " W", "WHITE")
                     Else: MsgBox ("dog7")
                     End If
                     End If
                     Model = Left$(Worksheets("sheet1").Range("Q" & FirstLine).Value, 6)
                     ModelName = Worksheets("Sheet1").Range("P" & FirstLine).Value
                     FormatManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-", " ")
                     FormatManufacturerColor = StrConv(FormatManufacturerColor, vbProperCase)
                     
                     Worksheets("sheet1").Range("N" & FirstLine).Value = FormatManufacturerColor
                     
                     FoundModel = Worksheets("sheet5").Cells.Range("DE1:DE65000").Find(Model).Row
                     FoundModelName = Worksheets("sheet5").Cells.Range("DC1:DC65000").Find(ModelName).Row

                
                     Worksheets("sheet1").Range("F" & FirstLine).Value = Range("Y" & StartRowNumber)
                     Worksheets("sheet1").Range("G" & FirstLine).Value = Range("AA" & StartRowNumber)
                     Worksheets("sheet1").Range("H" & FirstLine).Value = Range("AB" & StartRowNumber)
                     Worksheets("sheet1").Range("A" & FirstLine).Value = Range("AD" & StartRowNumber)
                     Worksheets("sheet1").Range("AC" & FirstLine).Value = Range("AC" & StartRowNumber)
                     
                   
                     
                     On Error GoTo ErrHandler:
                     FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor).Row
                     If Val(FoundManufacturerColor) > 0 Then
                        Worksheets("sheet1").Range("S" & FirstLine).Value = Worksheets("sheet5").Range("CS" & FoundManufacturerColor)
                        GoTo MainColorBreak
                     End If
                     
                     
Continue:
                Worksheets("Sheet1").Range("S" & FirstLine).Value = NewColor1
                
MainColorBreak:

                     If Search = "Model Number" Then
                     If Val(FoundModel) > 0 Then
                     Worksheets("sheet1").Range("M" & FirstLine).Value = Worksheets("sheet5").Range("CA" & FoundModel)
                     Worksheets("sheet1").Range("L" & FirstLine).Value = Worksheets("sheet5").Range("GR" & FoundModel)
                     Worksheets("sheet1").Range("K" & FirstLine).Value = Worksheets("sheet5").Range("AY" & FoundModel)
                     Worksheets("sheet1").Range("U" & FirstLine).Value = Worksheets("sheet5").Range("EI" & FoundModel)
                     Worksheets("sheet1").Range("V" & FirstLine).Value = Worksheets("sheet5").Range("EK" & FoundModel)
                     Worksheets("sheet1").Range("W" & FirstLine).Value = Worksheets("sheet5").Range("EM" & FoundModel)
                     Worksheets("sheet1").Range("X" & FirstLine).Value = Worksheets("sheet5").Range("GE" & FoundModel)
                     Worksheets("sheet1").Range("Y" & FirstLine).Value = Worksheets("sheet5").Range("GG" & FoundModel)
                     Worksheets("sheet1").Range("Z" & FirstLine).Value = Worksheets("sheet5").Range("CC" & FoundModel)
                     Worksheets("sheet1").Range("AA" & FirstLine).Value = Worksheets("sheet5").Range("CQ" & FoundModel)
                     Else: Worksheets("Sheet1").Range("M" & FirstLine).Value = "New"
                     End If
                     ElseIf Val(FoundModelName) > 0 Then
                            Worksheets("sheet1").Range("M" & FirstLine).Value = Worksheets("sheet5").Range("CA" & FoundModel)
                            Else: Worksheets("Sheet1").Range("M" & FirstLine).Value = "New"
                     End If
                     
                Next FirstLine

    
    

    NewFirstLine = LastLine
    
    

Next StartRowNumber
LastRow = Worksheets("sheet1").Cells(Rows.Count, "Q").End(xlUp).Row
Worksheets("sheet1").Rows(LastRow).Delete


ErrHandler:
If Not NewColor1 = NewColor Then
NewColor = InputBox("Manufacturer Color " & FormatManufacturerColor & " not found. Input New Main Color Here")
NewColor1 = NewColor
GoTo Continue:
Else
FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor).Row
GoTo Continue:
End If


End If

End Sub

Sorry for the mess...i am still very new to vba and need to develop better commenting/programming practices

[vbfbryce]

I think you need to clear the error before entering the loop the 2nd time:

Err.Clear

[slimjm911]

Thank you for your response. I tried using it after the err handler processes but is still didnt seem to work. Do i have to put it in a specific place?

[westconn1]

Do i have to put it in a specific place?

an error handler should clear it anyway if you use resume or resume next, instead of goto

Does the computer remember that that particular line of code threw an error and throws an error back at you?

absolutely not

FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor).Row

this will error if not item not found
you should use

set FoundManufacturerColor = Worksheets("sheet5").Cells.Range("CU1:CU65000").Find(FormatManufacturerColor)   ' range object
if not FoundManufacturerColor is nothing then FoundManufacturerColorRow = FoundManufacturerColor.row

to avoid error if the item is not found for any reason, including typing error in input box, do something else if not found

[slimjm911]

Thank you for your suggestion westconn. Your suggestion definitely helped. Once i changed the variable to a range object, i repeated the same process for two other necessary variables in my code and now it runs great. Does not throw the error anymore and allows me to handle the outcome a lot better. Thanks again! Any more tips on revising/improving my code?