|
-
Aug 14th, 2012, 02:29 PM
#1
Thread Starter
New Member
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
-
Aug 14th, 2012, 11:29 PM
#2
New Member
Re: Error Handling / Find Method
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 
Last edited by kobie2402; Aug 14th, 2012 at 11:45 PM.
-
Aug 15th, 2012, 04:34 AM
#3
Re: Error Handling / Find Method
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
Last edited by westconn1; Aug 15th, 2012 at 04:37 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed 
pete
-
Aug 20th, 2012, 03:21 PM
#4
Thread Starter
New Member
Re: Error Handling / Find Method
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
-
Aug 20th, 2012, 04:16 PM
#5
Re: Error Handling / Find Method
afaik find is not a worksheetfunction
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote