Find value, perform function, search for next value

[SLW612]

Hi, I am having trouble with writing a "find" code that will work. Here is
what I want to do:

I need to search column A for "Account Category:" and call out a macro on
that cell, then find the next instance and do the same. My worksheet is a
few thousand lines so having an automated find function will make a HUGE
difference! Part of the problem too is that the cells in question will
always begin with "Account Category:" but may differ, like one will be
"Account Category: 20512" and another might be "Account Category: 22265". I
tried recording a macro while I used the find function, but it wouldn't work
when I tried to run it.

Suggestions are greatly appreciated! Thanks in advance

 

[Jim Thomlinson]

Something like this should be close...

Sub test()
Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="Account Category:*", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
strFirstAddress = rngFound.Address
Do
rngFound.Select
MsgBox rngFound.Value & " found at " & rngFound.Address
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until strFirstAddress = rngFound.Address
End If
End Sub

 

[SLW612]

Thanks for the quick reply, Jim ... but it's not working. I get the
"Sorry...Not Found" every time, even when I am looking right at a cell that
begins with "Account Category:". When I type "Account Category:*" into the
find dialog it has no problems finding the search term, so there must be
something I'm missing ...

Any other ideas?

 

[Bob Phillips]

Try

Set rngFound = rngToSearch.Find(What:="Account Category:*", _
LookAt:=xlPart, _
LookIn:=xlValues, _
MatchCase:=False)


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLGWhiz]

I tried Jim's code and it worked fine. I copied his code from the posting,
set up a test shee with Account Category: 123, 234, 345 in three different
cells of column A and it found all three. Check your spelling, and make
sure that if you did not copy Jim's code, that your typed it exactly as he
wrote it, including the asterisk in the What:= .

 

[SLW612]

Ah, I found the problem. I should have specified that column A was merged
through to col. J - when I changed the range from "A" to "A:J" it found it
with no problems.

Thanks again!