Find problem

[Dave Birley]

Here's my Macro code (in part). All variables are DIMmed, their type is
indicated by the three letter prefix of the name.

strSearchItem = rngOuterCell.Value
Sheets("Earnings Balance 2003 Q4 Page 2").Select
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Set c = Selection.Find(What:=strSearchItem, LookIn:=xlValues)
If Not c Is Nothing Then
lngFoundRow = c.Row
GoTo Next1
End If

I have a collection of 8 WS, and if "c is Nothing", this process is repeated
with the next WS in the collection.

(Parenthetically, I have hard coded the WS name into an exact copy of the
above code, so I have 8 iteration of it. Messy, and it would be nice if I
could execute it in a loop that looks at each member of the Collection in
order -- but I don't know how to do it <g>).

Here is the real problem: When I run this against my first strSearchItem,
the sought after target is in Row 3 of the WS, immediately below the two-row
header. However when I step through it, in this instance, lngFoundRow is
always 4 not 3. WHen I run the code against the next item in the source list,
and strSearchItem is not in Row 3, it returns what I consider to be the
"correct" value.

The only thing I can think of that has been done to the data throughout, is
that it was Sorted manually some small time ago. but prior to this.

I also tried the search using this:

(What:=strSearchItem, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

And commenting out these:

'Range("C3").Select
'Range(Selection, Selection.End(xlDown)).Select

... same result.

Anyone got any ideas why I'm having this problem?

 

[Dave Birley]

Using an Array now for that "Parenthetically" part. All working just fine.
The other problem is still there.

 

[Tom Ogilvy]

Sub aBC()
Dim fndCell As Range, c As Range
Dim shts As Sheets, rng As Range
Dim strSearchItem As String
Dim ws As Worksheet
Set shts = Worksheets(Array("Sheet1", "Sheet5", "Sheet10"))
Set rngOuterCell = Worksheets("Sheet2").Range("A2")
strSearchItem = rngOuterCell.Value
For Each ws In shts
Set rng = ws.Range(ws.Range("C3"), _
ws.Range("C3").End(xlDown))

Set c = rng.Find(What:=strSearchItem, _
After:=rng(rng.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not c Is Nothing Then
lngFoundRow = c.Row
Set fndCell = c
Exit For
End If
Next
If fndCell Is Nothing Then
MsgBox "Not found"
Else
MsgBox "found at " & fndCell.Address(0, 0, xlA1, True)
End If

End Sub

worked fine for me.

 

[Dave Birley]

Thanks Tom, I'll study that closely and add it to my learning curve. My Array
works about the same as yours, but some of the other code is "more refined",
so I will need to learn how to "refine" my stuff.