using Find instead of VLOOKUP in search

[windsurferLA]

Using XL97, I’m 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. I’ve 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, I’m 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").Range("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

 

[Toppers]

Try:

Sub FindBillyBrown()
Dim vOurResult
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
If WorksheetFunction.CountIf(Worksheets("Sheet1").Range("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

 

[Toppers]

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

 

[Jim Thomlinson]

This should be close... It looks in column A for "This". It then places your
variable value 8th column over... If that is not quite what you wanted then
let me know... I could change it to look at the entire sheet and then place
your value in the 8th column but I have always found that type of code a
little dangerous as it finds things you never intended it to find...

Sub FindAll()
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Columns("A")
Set rngFound = rngToSearch.Find("This", , , xlPart)
If rngFound Is Nothing Then
MsgBox "Sorry... Nothing was found."
Else
Set rngFirst = rngFound
Do
wks.Cells( rngFound.row, 8).Value = vOurResult
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = rngFirst.Address
End If
End Sub

 

[windsurferLA]

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

 

[windsurferLA]

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.