View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
michael.beckinsale michael.beckinsale is offline
external usenet poster
 
Posts: 274
Default 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