The following mocked up macro is working for the first piece of searchable criteria (returning a blank cell for the loops in which the string is not found), but when duplicated exactly for the remaining 25 pieces of criteria it needs to search for, it will not return a blank cell. Instead, it will grab the data found in the next loop and duplicate it for both. I cannot figure out why it would work for the first one, and not for the rest when it is written exactly the same way...any help would be so greatly appreciated:
Sub Loops()
S = 1
P = 1
LR = Cells(Rows.Count, 1).End(xlUp).Row
R = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
Loop1:
S = Range(Cells(S, 1), Cells(LR, 1)).Find("SR~*530~*", LookIn:=xlValues, Lookat:=xlPart).Row
R(0) = Cells(S, 1).Value
E = S
Loop2:
E = Range(Cells(E, 1), Cells(LR, 1)).Find("TE~*", LookIn:=xlValues, Lookat:=xlPart).Row
If UCase(Left(Cells(E, 1), 2)) <> "TE" Then GoTo Loop2
On Error Resume Next
Set pos = Range(Cells(S, 1), Cells(E, 1)).Find("GRO~*", LookIn:=xlValues, Lookat:=xlPart)
If IsError(InStr(pos, "GRO~*")) Then
R(1) = ""
Else
Q = Split(pos, "*")
'***********************
R(1) = Q(8)
'***********************
End If
On Error Resume Next
NextOne = Range(Cells(S, 1), Cells(LR, 1)).Find("…
**Repeats for all 26 ranges**
…End If
Range(Cells(P, 4), Cells(P, 31)).Value = R
P = P + 1
S = Range(Cells(S, 1), Cells(LR, 1)).Find("TE~*", LookIn:=xlValues, Lookat:=xlPart).Row
If S < LR Then GoTo Loop1
End Sub
Bookmarks