Error Handling / Find Method

Hey Guys,

I am extremely new to VBA , probably about three days in now, and cant seem to get around a problem i am having with the Find Function/Method

Everything works pretty well until i get to the this set of lines:

On Error GoTo ErrHandler:
FoundManufacturerColor = Worksheets("sheet5").WorksheetFunction.Find(What:=FormatManufacturerColor, LookIn:=xlValues, LookAt:=xlWhole)
FoundManufacturerColorRow = Worksheets("sheet5").FoundManufacturerColor.Row
MsgBox FoundManufacturerColorRow

It seems that the Find Method is not "finding" the variable FormatManufacturerColor within sheet5 when it clearly is there. It skips straight to the ErrHandler Section when it should be continuing on to the next line.
Being the noob that i am, i am sure i am missing something silly but cant seem to put my finger on it. If you guys could help that would be great!!!!

Also, if you guys had any tips on making this code run faster or more efficient i would love to hear it. I dont know why i haven't put comments x0 but if you need clarification about anything let me know. I also have a spreadsheet if you guys need to reference or run the code on. Thanks again for you time!!!

Code:

---


Sub Fomat()

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

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
               
                    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)
                    Model = Left$(Worksheets("sheet1").Range("Q" & FirstLine).Value, 6)
                    ModelName = Worksheets("Sheet1").Range("P" & FirstLine).Value
                    FormatManufacturerColor = WorksheetFunction.Substitute(ManufacturerColor, "-", " ")
                    FormatManufacturerColor = StrConv(FormatManufacturerColor, vbProperCase)
                    FoundModel = Worksheets("sheet5").Cells.Range("DE1:DE65000").Find(Model).Row
                    FoundModelName = Worksheets("sheet5").Cells.Range("DC1:DC65000").Find(ModelName).Row
                   
                   
                   
                   
                    On Error GoTo ErrHandler:
                    FoundManufacturerColor = Worksheets("sheet5").WorksheetFunction.Find(What:=FormatManufacturerColor, LookIn:=xlValues, LookAt:=xlWhole)
                    FoundManufacturerColorRow = Worksheets("sheet5").FoundManufacturerColor.Row
                    MsgBox FoundManufacturerColorRow
                   
                   
        If Not Val(FoundManufacturerColor) = 0 Then
                        Worksheets("sheet1").Range("S" & FirstLine).Value = Worksheets("sheet5").Range("CS" & FoundManufacturerColor)
                        Else: Worksheets("sheet1").Range("S" & FirstLine).Value = "New"
                    End If
                   
ErrHandler:
NewColor = InputBox("Manufacturer Color " & FormatManufacturerColor & " not found. Input New Main Color Here")
Worksheets("sheet1").Range("S" & FirstLine).Value = NewColor
Resume Next
               

                    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

    Application.EnableCancelKey = xlInterrupt
   

    NewFirstLine = LastLine
   
   

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






End Sub

---

Slimjim,

Try adding an "end if" as shown here:

Code:

---

Sub Fomat()

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"

End If

---

I dont have any problems as of yet.. but I dont have the file that you are working with either :)

try like, to handle if not found

Code:

---

On Error GoTo ErrHandler:
                    set FoundManufacturerColor = Worksheets("sheet5").WorksheetFunction.Find(What:=FormatManufacturerColor, LookIn:=xlValues, LookAt:=xlWhole)
                    if not FoundManufacturerColor is nothing then
                      FoundManufacturerColorRow = Worksheets("sheet5").FoundManufacturerColor.Row
                      MsgBox FoundManufacturerColorRow
                    else
                      msgbox "not found" ' do whatver
                    end if

---

you do not specify what version of excel you are using, i would believe, below 2010 you would need the Set keyword

Thanks for the responses guys. I am using excel 2010, sorry about forgetting to mention that. When i use your code westconn1 i get an error saying "object required". This happens at the line:

Code:

---

set FoundManufacturerColor = Worksheets("sheet5").WorksheetFunction.Find(What:=FormatManufacturerColor, LookIn:=xlValues, LookAt:=xlWhole)

---

Do not understand why the code isnot working. I have something similar set up above it for model name and model number as well

afaik find is not a worksheetfunction