Object or With Block Variable Not Set

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:

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:

Code:

---

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:

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

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

Code:

---

Err.Clear

---

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?

Quote:

---

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

Quote:

---

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

---

absolutely not

Quote:

---

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

---

this will error if not item not found
you should use

Code:

---

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

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?