Query with Excel VBA Find Method
 

Hello,

I have the following code that searches for a particular string in
column K and repeats the process until all have been found (or that's
what I intended).

In one workbook, the first instance is found at K330 with the next 8
rows containing the same value.
The address of rngPO changes after each find and after the last one
the address of rngPO changes back to K330.

Could someone please advise how I can overcome this issue?


With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),
LookIn:=xlValues)
While Not (IsEmpty(rngPO))

rngPO.Select

Code to process each occurrence

Set rngPO = .FindNext(rngPO)

Wend
End With

Thank you
Mark

Query with Excel VBA Find Method
 

Hi Mark,

You could put in a 'memo' variable & IF statement, something like
this,

With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),LookIn:=xlValues)
While Not (IsEmpty(rngPO))
rngPO.Select
Code to process each occurrence


Set rngPO = .FindNext(rngPO)


Wend
End With





rngPO1 = RngPO

IF rng

Query with Excel VBA Find Method
 

Hi Mark,

Sorry accidentally hit the submit key before l had finished replying.

See code below. Untested but should point you in the right direction.

With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),LookIn:=xlValues)
rngPO1 = rngPO
While Not (IsEmpty(rngPO))
rngPO.Select
'Code to process each occurrence
Set rngPO = .FindNext(rngPO)
If rngPO = rngPO1 Then
' exit sub / do something else, all instances found & processed.
End IF
Wend
End With

I notice that you have selected rngPO before proceesing each
occurence. Selecting ranges is not usually necessary and slows down
processing time, it will be more efficient to do something directly
with the rngPO.

HTH

Regards

Michael

Query with Excel VBA Find Method
 

You need to accumulate the cells as you Find them:

Before the While, insert:

Dim accumulator As Range
Set accumulator = Nothing

After the While, insert:

If accumulator Is Nothing Then
Set accumulator = rngPO
Else
Set accumulator = Union(accumulator, rngPO)
End If


--
Gary''s Student - gsnu200776


"MarkM" wrote:

Hello,

I have the following code that searches for a particular string in
column K and repeats the process until all have been found (or that's
what I intended).

In one workbook, the first instance is found at K330 with the next 8
rows containing the same value.
The address of rngPO changes after each find and after the last one
the address of rngPO changes back to K330.

Could someone please advise how I can overcome this issue?


With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),
LookIn:=xlValues)
While Not (IsEmpty(rngPO))

rngPO.Select

Code to process each occurrence

Set rngPO = .FindNext(rngPO)

Wend
End With

Thank you
Mark

Query with Excel VBA Find Method
 

I have used cdo---loop instead of while---wend
try tis mdoified macro
Sub test()
Dim rngpo As Range
Dim strpo As String
Dim add As String
strpo = "a" ' this is only an example
With ActiveSheet.Range("K1:K5000")
Set rngpo = .Find(strpo, After:=Cells(1, 11), _
LookIn:=xlValues, lookat:=xlWhole)
add = rngpo.Address
rngpo.Select

'Code to process each occurrence
Do

Set rngpo = .FindNext(rngpo)
If rngpo.Address = add Then GoTo line1
rngpo.Select
Loop While Not (IsEmpty(rngpo)) And rngpo.Address <
add
line1:
End With
End Sub

=================================================
MarkM wrote:
Hello,

I have the following code that searches for a particular string in
column K and repeats the process until all have been found (or that's
what I intended).

In one workbook, the first instance is found at K330 with the next 8
rows containing the same value.
The address of rngPO changes after each find and after the last one
the address of rngPO changes back to K330.

Could someone please advise how I can overcome this issue?


With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),
LookIn:=xlValues)
While Not (IsEmpty(rngPO))

rngPO.Select

Code to process each occurrence

Set rngPO = .FindNext(rngPO)

Wend
End With

Thank you
Mark

Query with Excel VBA Find Method
 

Option Explicit
Sub testme()

Dim RngToSearch As Range
Dim FirstAddress As String
Dim StrPO As String
Dim RngPO As Range

Set RngToSearch = ActiveSheet.Range("K1:k5000") '(not K:K???)

StrPO = "something"

With RngToSearch
'xlwhole or xlpart????
'it's better to explicitly use all the parms.
'otherwise you inherit the settings from the last Find
'either from the user or code!
Set RngPO = .Cells.Find(what:=StrPO, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
MatchByte:=False)

If RngPO Is Nothing Then
MsgBox "not found!"
Else
FirstAddress = RngPO.Address
Do

'code to process each occurrence
'it's not usually necessary to select this range

'look for more
Set RngPO = .FindNext(after:=RngPO)

If RngPO Is Nothing Then
Exit Do
End If

If RngPO.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With

End Sub

MarkM wrote:

Hello,

I have the following code that searches for a particular string in
column K and repeats the process until all have been found (or that's
what I intended).

In one workbook, the first instance is found at K330 with the next 8
rows containing the same value.
The address of rngPO changes after each find and after the last one
the address of rngPO changes back to K330.

Could someone please advise how I can overcome this issue?

With ActiveSheet.Range("K1:K5000")
Set rngPO = .Find(strPO, After:=Cells(1,11),
LookIn:=xlValues)
While Not (IsEmpty(rngPO))

rngPO.Select

Code to process each occurrence

Set rngPO = .FindNext(rngPO)

Wend
End With

Thank you
Mark

--

Dave Peterson