Trying to automate a lengthy data entry process in excel.
For some reason the second time I try to find a value in a given range I get a run time 91 error. I can't figure out why this is happening. If I change the range from "AF3:AF30" to the first time I set a range("B2:AD2"), the program will continue through the line with no problem. But for some reason any range other than the first "B2:AD2" will give runtime!
Code:
Sub AutoFill()
Range("B8:AD57").Select
Selection.ClearContents
Range("B8").Select
Dim Rng As Range, cell As Range
Set Rng = Worksheets("Avon Trailer List").Range("E4:E38")
For Each cell In Rng
Dim temp As String
temp = Replace(cell.Value(), " ", "")
temp = Replace(temp, "+", "")
If temp = "" Then
Exit For
Else
col = ""
tempa = SplitMultiDelims(temp, "/,)(")
For i = 0 To UBound(tempa)
If InStr(tempa(i), "A") = 1 Then
strInput = Worksheets("Pallet Check").Range("B2:AD2").Find(tempa(i)).Address()
lnRow = Range(strInput).Row
strCol = Left(strInput, Len(strInput) - Len(CStr(lnRow)))
strCol = Replace(strCol, "S", "")
Search = tempa(i)
ElseIf InStr(tempa(i), "A") > 1 Or InStr(tempa(i), "B") > 1 Or InStr(tempa(i), "a") > 1 Or InStr(tempa(i), "b") > 1 Then
strInput = Worksheets("Pallet Check").Range("AF3:AF30").Find(Search).Address()
lnRow = Range(strInput).Row
Range("AH" & lnRow).Value() = "(" & tempa(i) & ")"
ElseIf tempa(i) <> "" Then
Worksheets("Pallet Check").Range(strCol & tempa(i) + 7).Value() = Worksheets("Pallet Check").Range(strCol & tempa(i) + 7).Value() & tempa(i)
End If
Next i
End If
Next cell
End Sub
Runtime Error 91 happens on the following line of code:
strInput = Worksheets("Pallet Check").Range("AF3:AF30").Find(Search).Address()
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…