Hi,

Can anyone help with me a problem I am having with the find function
in VBA? The following code seems to work...

On Error Resume Next

X = Cells.Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

MsgBox (X)

If "Name" is present, this will return true, if name is not present,
the message box is blank.

This is fine, but I need to loop through this statement as I am trying
to automate the process of todying up data from an unformatted txt
file to another worksheet. There are not always the same amount of
names in the sheets so it needs to be able to keep searching until
there are no more matches. So my idea was this (obviously I want to do
more than just display a message box when I find the cell, but you get
the idea...)

On Error Resume Next

Dim i
i = 1

Do

X = Cells.Find(What:="Name" & i, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

MsgBox (X)

i = i +1

Loop Until x < "True"

It now seems to continuously return true, even when the value does not
appear on the page. Maybe I am going about this all the wrong way, but
has anyone come up against this sort of need for a loop, and could you
help me with why this won't work, or indeed advise of another way of
what I need to do. If you need me to be any more specific, just let me
know, but I think this covers it.

Cheers
KK

Review the example code in Help for the Find method.
Note the use of the Set statement and the FindNext method...
'--
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
'--

Also, avoid the use of On Error Resume Next.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - "Find and List" - find multiple items on multiple sheets)




"StraightEight"
wrote in message
Hi,
Can anyone help with me a problem I am having with the find function
in VBA? The following code seems to work...

On Error Resume Next
X = Cells.Find(What:="Name", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox (X)

If "Name" is present, this will return true, if name is not present,
the message box is blank.
This is fine, but I need to loop through this statement as I am trying
to automate the process of todying up data from an unformatted txt
file to another worksheet. There are not always the same amount of
names in the sheets so it needs to be able to keep searching until
there are no more matches. So my idea was this (obviously I want to do
more than just display a message box when I find the cell, but you get
the idea...)
On Error Resume Next

Dim i
i = 1
Do

X = Cells.Find(What:="Name" & i, After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox (X)
i = i +1
Loop Until x < "True"

It now seems to continuously return true, even when the value does not
appear on the page. Maybe I am going about this all the wrong way, but
has anyone come up against this sort of need for a loop, and could you
help me with why this won't work, or indeed advise of another way of
what I need to do. If you need me to be any more specific, just let me
know, but I think this covers it.
Cheers
KK

My apologies for the delayed response, this was indeed (after a bit of
tweaking, headaches and frustration!) the correct method for the task
I was needing to do. Many thanks for pointing me where I should have
looked first!

Regards,
Str8

On 4 Feb, 13:37, "Jim Cone" wrote:
Review the example code in Help for the Find method.
Note the use of the Set statement and the FindNext method...
'--
With Worksheets(1).Range("a1:a500")
* * Set c = .Find(2, lookin:=xlValues)
* * If Not c Is Nothing Then
* * * * firstAddress = c.Address
* * * * Do
* * * * * * c.Interior.Pattern = xlPatternGray50
* * * * * * Set c = .FindNext(c)
* * * * Loop While Not c Is Nothing And c.Address < firstAddress
* * End If
End With
'--

Also, avoid the use of On Error Resume Next.
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - "Find and List" - find multiple items on multiple sheets)