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

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." <(E-Mail Removed)> wrote in message news:09FF69C1-3D69-408F-B2B8-(E-Mail Removed)...
> 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
>

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." <(E-Mail Removed)> wrote in message news:09FF69C1-3D69-408F-B2B8-(E-Mail Removed)...
> > 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
> >
>

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