How do you stop the Find method?
 

I'm new to Excel programming . . . The following procedure finds a string in
a column, then goes a couple columns over (determined via user input) and
enters a category.

The procedure finds the appropriate entry and enters the text in a column,
on the same row, as it should.

However, it continues to loop through the spreadsheet, until I do a
Ctrl+Break.

How do I determine when the Find method has gone through the worksheet once.

Thanks in advance,

Rich

This is the loop:
Do
Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False).Activate
CellAddress = txtOutputCol & ActiveCell.Row
Range(CellAddress).Select
ActiveCell.Value = txtReplString
Loop Until Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False) < txtSrchString.Text

How do you stop the Find method?
 

Hi Rich

See how I do it in this example

http://www.rondebruin.nl/find.htm#Mark

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rich W." wrote in message ...
I'm new to Excel programming . . . The following procedure finds a string in
a column, then goes a couple columns over (determined via user input) and
enters a category.

The procedure finds the appropriate entry and enters the text in a column,
on the same row, as it should.

However, it continues to loop through the spreadsheet, until I do a
Ctrl+Break.

How do I determine when the Find method has gone through the worksheet once.

Thanks in advance,

Rich

This is the loop:
Do
Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False).Activate
CellAddress = txtOutputCol & ActiveCell.Row
Range(CellAddress).Select
ActiveCell.Value = txtReplString
Loop Until Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False) < txtSrchString.Text

How do you stop the Find method?
 

Thanks Ron . . .

I read Daily Dose of Excel; I see your name from time to time, and will have
to use your site as a resource . . .

Thanks again,

Rich

"Ron de Bruin" wrote:

Hi Rich

See how I do it in this example

http://www.rondebruin.nl/find.htm#Mark

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rich W." wrote in message ...
I'm new to Excel programming . . . The following procedure finds a string in
a column, then goes a couple columns over (determined via user input) and
enters a category.

The procedure finds the appropriate entry and enters the text in a column,
on the same row, as it should.

However, it continues to loop through the spreadsheet, until I do a
Ctrl+Break.

How do I determine when the Find method has gone through the worksheet once.

Thanks in advance,

Rich

This is the loop:
Do
Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False).Activate
CellAddress = txtOutputCol & ActiveCell.Row
Range(CellAddress).Select
ActiveCell.Value = txtReplString
Loop Until Cells.Find(What:=txtSrchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True, _
SearchFormat:=False) < txtSrchString.Text

How do you stop the Find method?
 

Hey Rich,

I would do a do until or do while loop. I like these more because it
sets parameters and tells the loop to stop at a certain point. For
me, it's usually when it gets to blank or "".

Hope that helped.

Kevin