Set range, perform operation, set new range, repeat

[CompleteNewb]

Excel 2003/2007, using VBA:

I got this working to find the text "MatchText" in the I column of the
worksheet "SheetName", go down 2 cells, and create a range from that cell to
the last used cell in column I:

Set TempRange = Worksheets("SheetName").Range("I" &
Application.Match("MatchText", Worksheets("SheetName").Range("I:I"), 0) + 2,
Worksheets("SheetName").Range("I65536").End(xlUp))

I've been trying to use modifications of this to do the following, but
nothing works.

1) How can I set a range by:
a) Find the first occurence of a specific (string) value in Column D
b) From that found match, find a match for a different string below but in
Column B, and START the range there
c) From my Range start, go to column AA and find the first blank cell in
that column and down from my StartRange row.

Example: 1st string to match = "1stString"
2nd string to match = "2ndString"

So in column D I find the first occurrence of "1stString" in D22. Now,
underneath that match and in column
B I need to find 1st occurrence of "2ndString". I find that in B26. So my
range begins at B26.
Now, in column AA I need to find the 1st blank cell after row 26 (where my
range has started)
The first blank cell after row 26 and in column AA is AA36.
So, my range is Range(B26:AA36). Then I do some VLookups/Index/Match based
on values in this range and on other sheets, then when I'm done, and
starting at D37 (because the last row in my previous range was 36), I look
for "1stString" again, and it all repeats until there are no longer
occurrences of "1stString" in D.

Can someone help me figure out how to do this "Set range based on found
values and other found values, etc."? I appreciate any help, and thanks for
reading.

 

[Per Jessen]

Hi

First, I will rewrite your original code, using a With statement and
second I will use .Find to search for the string, see below:

1)
With Worksheets("SheetName")
Set temprange1 = .Range("I" &
Application.Match("MatchText", .Range("I:I"), 0) +
2, .Range("I65536").End(xlUp))
End With

2)
With Worksheets("SheetName")
Set temprange2 = .Range(.Range("I:I").Find(what:="MatchText",
After:=.Range("I1"), _
lookat:=xlWhole).Offset(2), .Range("I" &
Rows.Count).End(xlUp))
End With

With theese techniques in memory, the code below should do what your
need:

Sub ddd()
Dim TempRange As Range
Dim aaRow As Long

With Worksheets("SheetName")
Set f = .Range("D").Find(What:="1stString", After:=.Range("D1"),
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False)
Set fFound = f
Do
If Not f Is Nothing Then
Set f1 = .Range(f.Offset(0, -2), .Range("B" &
Rows.Count).End(xlUp)) _
.Find(What:="2ndString", After:=f.Offset(0, -2),
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False)

Set TempRange = Range(f1, .Range("AA" &
f.Row).End(xlDown).Offset(1, 0))
aaRow = TempRange.Row + TempRange.Rows.Count - 1
End If
'Here goes your Vlookups etc.

Set f = .Range("D").Find(What:="1stString",
After:=.Range("D" & aaRow))

Loop Until f.Address = fFound.Address
End With

End Sub

Regards,
Per