My earlier update somehow did not get posted. .

I found that if I use Thomlinson's suggestion of the form:

Set rngFound = rngToSearch.Find("This", , , xlPart)

Where rngFound was defined as a range.

Then:
Worksheets("Sheet1").Cells(ActiveCell.Row, 10).Value = rngFound.Row
would yield the row.

Thus I concluded that XL97 would accept rngFound.Row

So then I tried:
rngFound = Range("Data").Find(What:="Mary Smith", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)

where rngFound was defined as a range.. When it was followed by the
following statement, I got zip

Worksheets("Sheet1").Cells(ActiveCell.Row, 10).Value = rngFound.Row
would yield the row.

even though "Mary Smith" was within the Range("Data").


Any ideas what I'm doing wrong.

windsurferLA wrote:
Problem with your proposed solution:

XL97 does not digest: "MsgBox vOurResult.Row"

it flags it as an error,
and thus
Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult
does not work.

obviously XL97, it does not track where a result came from.
I can designate where to save "vOurResult"
but I have yet to figure out how to retrive from the find command the
row in which the search item was found.

Michael Ernstoff

Toppers wrote:

Try:

Sub FindBillyBrown()
Dim vOurResult
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"),
"BillyBrown") 0 Then
With Worksheets("Sheet1").Range("Data")
Set vOurResult = .Find(What:="BillyBrown", After:=.Cells(1,
1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With

MsgBox vOurResult.Row

Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult

End If

End Sub

"windsurferLA" wrote:


Using XL97, Im seeking to search through a list to find those
records that match a criteria, and then for each matching record,
place the result in column 8 of that record. Ive been seeking to
use the find function as it supposedly runs much faster than the
VLOOKUP function. The code shown below performs the find function for
one occurrence. When I learn how to make it work, Im confident I can
include it in a loop to repeat the operation for multiple occurrences. :

HOW DO I set the value of the cell in the eighth column of the
matching record to the vOurResult???? If tried things like:

Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult

But the above line places the result on the line where the cursor
happens to be instead of the line containing the matching record.

(Code is plagiarized from:
http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm)



Sub FindBillyBrown()
Dim vOurResult
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"),
"Billy Brown") 0 Then
With Worksheets("Sheet1").Range("Data")

vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)

End With

MsgBox vOurResult
End If
End Sub