P.S I'm using XL2003 so not so sure how FIND works with XL97.

"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