I have created a macro that looks for the word Total that is in Bold type.
When it finds this, it will create a new worksheet, copy a header from a
template that is in the workbook and format the worksheet for printing. I
have copied the steps and pasted those same steps several times in the macro.
The problem is, that the number of times that this formatted Total is found
is not consistent. So sometimes the Total may be there three times and
sometimes 5. So the macro creates an error if it tries to run too many times
and stops once it gets through the number of times that I have copied the
code in the macro.

I was thinking that if I created the steps in a separate macro and have the
macro run if it finds the formatted word that would be much simpler. So I
would like to create a loop that looks for this formatted text and run a
macro if it finds it. If it doesn't, I would like the whole macro to stop.

The code that I have that looks for the text is as follows:

With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate
With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate

As you can see, I have it search for the text twice because each time after
it performs the rest of the macro, it finds the same text that it perfoms the
macro on first and then it finds the next formatted text to perform the rest
of the macro again. How would I create a loop to tell it to look for this
text twice and if not found to stop.

Any help would be appreciated.

Rob

On Fri, 16 Feb 2007 05:30:00 -0800, =?Utf-8?B?Um9i?=
<(E-Mail Removed)> wrote:

>I have created a macro that looks for the word Total that is in Bold type.
>When it finds this, it will create a new worksheet, copy a header from a
>template that is in the workbook and format the worksheet for printing. I
>have copied the steps and pasted those same steps several times in the macro.
> The problem is, that the number of times that this formatted Total is found
>is not consistent. So sometimes the Total may be there three times and
>sometimes 5. So the macro creates an error if it tries to run too many times
>and stops once it gets through the number of times that I have copied the
>code in the macro.
>
>I was thinking that if I created the steps in a separate macro and have the
>macro run if it finds the formatted word that would be much simpler. So I
>would like to create a loop that looks for this formatted text and run a
>macro if it finds it. If it doesn't, I would like the whole macro to stop.
>
>The code that I have that looks for the text is as follows:
>
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
>MatchCase:=False _
> , SearchFormat:=True).Activate
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
>MatchCase:=False _
> , SearchFormat:=True).Activate
>
>As you can see, I have it search for the text twice because each time after
>it performs the rest of the macro, it finds the same text that it perfoms the
>macro on first and then it finds the next formatted text to perform the rest
>of the macro again. How would I create a loop to tell it to look for this
>text twice and if not found to stop.
>
>Any help would be appreciated.
>
>Rob
>

Does this help?

Sub search()

Dim rownum As Integer

rownum = 1
On Error GoTo done
Cells(1, 1).Select

Do
rownum = Application.ActiveCell.Row

With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
Cells.Find(What:="Total", After:=ActiveCell, _
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate


If Application.ActiveCell.Row >= rownum Then

' Do the new sheet here

End If

Loop Until Application.ActiveCell.Row < rownum

done:

End Sub


- Jussi -

Dim rng as Range, sAddr as String
With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
set rng = Cells.Find(What:="Total", _
After:=Range("IV65536"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)
if not rng is nothing then
sAddr = rng.Address
do
rng.Select
' code to process rng
' find next instance
set rng = cells.Findnext(rng)
Loop while rng.Address <> sAddr
end if

--
Regards,
Tom Ogilvy

"Rob" <(E-Mail Removed)> wrote in message
news:CDECD170-F605-44FA-A298-(E-Mail Removed)...
>I have created a macro that looks for the word Total that is in Bold type.
> When it finds this, it will create a new worksheet, copy a header from a
> template that is in the workbook and format the worksheet for printing. I
> have copied the steps and pasted those same steps several times in the
> macro.
> The problem is, that the number of times that this formatted Total is
> found
> is not consistent. So sometimes the Total may be there three times and
> sometimes 5. So the macro creates an error if it tries to run too many
> times
> and stops once it gets through the number of times that I have copied the
> code in the macro.
>
> I was thinking that if I created the steps in a separate macro and have
> the
> macro run if it finds the formatted word that would be much simpler. So I
> would like to create a loop that looks for this formatted text and run a
> macro if it finds it. If it doesn't, I would like the whole macro to
> stop.
>
> The code that I have that looks for the text is as follows:
>
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:=
> _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=True).Activate
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:=
> _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=True).Activate
>
> As you can see, I have it search for the text twice because each time
> after
> it performs the rest of the macro, it finds the same text that it perfoms
> the
> macro on first and then it finds the next formatted text to perform the
> rest
> of the macro again. How would I create a loop to tell it to look for this
> text twice and if not found to stop.
>
> Any help would be appreciated.
>
> Rob
>

On Feb 16, 7:30 am, Rob <R...@discussions.microsoft.com> wrote:
> I have created a macro that looks for the word Total that is in Bold type.
> When it finds this, it will create a new worksheet, copy a header from a
> template that is in the workbook and format the worksheet for printing. I
> have copied the steps and pasted those same steps several times in the macro.
> The problem is, that the number of times that this formatted Total is found
> is not consistent. So sometimes the Total may be there three times and
> sometimes 5. So the macro creates an error if it tries to run too many times
> and stops once it gets through the number of times that I have copied the
> code in the macro.
>
> I was thinking that if I created the steps in a separate macro and have the
> macro run if it finds the formatted word that would be much simpler. So I
> would like to create a loop that looks for this formatted text and run a
> macro if it finds it. If it doesn't, I would like the whole macro to stop.
>
> The code that I have that looks for the text is as follows:
>
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=True).Activate
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=True).Activate
>
> As you can see, I have it search for the text twice because each time after
> it performs the rest of the macro, it finds the same text that it perfoms the
> macro on first and then it finds the next formatted text to perform the rest
> of the macro again. How would I create a loop to tell it to look for this
> text twice and if not found to stop.
>
> Any help would be appreciated.
>
> Rob

This worked for me...

Sub newPage()
Dim x As Integer
With Application.FindFormat.Font
.FontStyle = "Bold"
.Subscript = False
End With
On Error GoTo error_end
For x = 1 To 1000 Step 1
' I used 1000, but this could be set to the number
' of cells in row A if you wanted.

Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=True).Activate

'INSERT YOUR CODE HERE
Next x
error_end:

End Sub

That worked perfectly. Thanks so much. I was wondering if I could ask you
another question. I have another file that I need to incorporate a vlookup
in a macro. I am writing the code now but the Vlookup portion does not seem
to work. I will post my problem this afternoon. I would appreciate your
help. I can't believe that was so easy.

Thanks again!!

Rob

"OKROB" wrote:

> On Feb 16, 7:30 am, Rob <R...@discussions.microsoft.com> wrote:
> > I have created a macro that looks for the word Total that is in Bold type.
> > When it finds this, it will create a new worksheet, copy a header from a
> > template that is in the workbook and format the worksheet for printing. I
> > have copied the steps and pasted those same steps several times in the macro.
> > The problem is, that the number of times that this formatted Total is found
> > is not consistent. So sometimes the Total may be there three times and
> > sometimes 5. So the macro creates an error if it tries to run too many times
> > and stops once it gets through the number of times that I have copied the
> > code in the macro.
> >
> > I was thinking that if I created the steps in a separate macro and have the
> > macro run if it finds the formatted word that would be much simpler. So I
> > would like to create a loop that looks for this formatted text and run a
> > macro if it finds it. If it doesn't, I would like the whole macro to stop.
> >
> > The code that I have that looks for the text is as follows:
> >
> > With Application.FindFormat.Font
> > .FontStyle = "Bold"
> > .Subscript = False
> > End With
> > Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False _
> > , SearchFormat:=True).Activate
> > With Application.FindFormat.Font
> > .FontStyle = "Bold"
> > .Subscript = False
> > End With
> > Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
> > xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> > MatchCase:=False _
> > , SearchFormat:=True).Activate
> >
> > As you can see, I have it search for the text twice because each time after
> > it performs the rest of the macro, it finds the same text that it perfoms the
> > macro on first and then it finds the next formatted text to perform the rest
> > of the macro again. How would I create a loop to tell it to look for this
> > text twice and if not found to stop.
> >
> > Any help would be appreciated.
> >
> > Rob
>
> This worked for me...
>
> Sub newPage()
> Dim x As Integer
> With Application.FindFormat.Font
> .FontStyle = "Bold"
> .Subscript = False
> End With
> On Error GoTo error_end
> For x = 1 To 1000 Step 1
> ' I used 1000, but this could be set to the number
> ' of cells in row A if you wanted.
>
> Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues,
> LookAt:= _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
> MatchCase:=False _
> , SearchFormat:=True).Activate
>
> 'INSERT YOUR CODE HERE
> Next x
> error_end:
>
> End Sub
>
>