Error Handling / Find Method

**slimjm911** · Aug 14th, 2012, 02:29 PM

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

**kobie2402** · Aug 14th, 2012, 11:29 PM

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

**westconn1** · Aug 15th, 2012, 04:34 AM

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

**slimjm911** · Aug 20th, 2012, 03:21 PM

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

**westconn1** · Aug 20th, 2012, 04:16 PM

afaik find is not a worksheetfunction

Thread: Error Handling / Find Method

Thread Tools

Display

Error Handling / Find Method

Re: Error Handling / Find Method

Re: Error Handling / Find Method

Re: Error Handling / Find Method

Re: Error Handling / Find Method

Posting Permissions