Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA : Find Method | Excel Programming | |||
Excel VBA .Find method - am I clueless? | Excel Programming | |||
Excel Find Method | Excel Programming | |||
Proper method for Excel/Query/Macro reporting | Excel Programming | |||
Problems with Excel Web Query for Post Method URL | Excel Programming |