Thread: Do Until Macro

I would use a FINDNEXT macro to find all instances. You don't need to look
for "summary"

Sub findsubtotals()
mc = 1 'col A
lr = Cells(Rows.Count, mc).End(xlUp).Row
'With Range("a1:a" & lr)
With Cells(1, mc).Resize(lr)
Set c = .find(What:="subtotal", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox c.Row
c.Offset(, 7).Resize(, 4).Borders.LineStyle = xlContinuous
Rows(c.Row).Font.Bold = True

Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Flipper" <Flipper@discussions.microsoft.com> wrote in message
news:0F9E6FD2-ED4B-443C-BE7F-824ABAC7F457@microsoft.com...
> Hello
>
> I would like to write a "Do Until" macro that will search for the word
> "Sub-total" and after finding it. move 7 columns over, then select the
> next
> four cells to the right and apply a certain border to those cells. After
> that, I would like that entire row formatted with Bold. I would want to
> repeat these steps until it comes to the word Summary.
>
> For example find subtotal (which in this case is in cell B10), then move
> over 7 columns to cell I10, select I10:L10, then apply formatting to those
> cells. after that select row 10 and apply bold format.
>
> I know this should be easy, but I have never done a looping macro.
>
> Thanks
> --
> Allan

What does "mc" mean?
--
Allan


"Don Guillett" wrote:

> I would use a FINDNEXT macro to find all instances. You don't need to look
> for "summary"
>
> Sub findsubtotals()
> mc = 1 'col A
> lr = Cells(Rows.Count, mc).End(xlUp).Row
> 'With Range("a1:a" & lr)
> With Cells(1, mc).Resize(lr)
> Set c = .find(What:="subtotal", LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
>
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
> MsgBox c.Row
> c.Offset(, 7).Resize(, 4).Borders.LineStyle = xlContinuous
> Rows(c.Row).Font.Bold = True
>
> Set c = .FindNext(c)
> Loop While Not c Is Nothing _
> And c.Address <> firstAddress
> End If
> End With
> End Sub
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Flipper" <Flipper@discussions.microsoft.com> wrote in message
> news:0F9E6FD2-ED4B-443C-BE7F-824ABAC7F457@microsoft.com...
> > Hello
> >
> > I would like to write a "Do Until" macro that will search for the word
> > "Sub-total" and after finding it. move 7 columns over, then select the
> > next
> > four cells to the right and apply a certain border to those cells. After
> > that, I would like that entire row formatted with Bold. I would want to
> > repeat these steps until it comes to the word Summary.
> >
> > For example find subtotal (which in this case is in cell B10), then move
> > over 7 columns to cell I10, select I10:L10, then apply formatting to those
> > cells. after that select row 10 and apply bold format.
> >
> > I know this should be easy, but I have never done a looping macro.
> >
> > Thanks
> > --
> > Allan
>
> .
>

Think about it
mc = 1 'col A


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Flipper" <Flipper@discussions.microsoft.com> wrote in message
news:56135BDE-D237-4DF7-82B0-9B442C9580A8@microsoft.com...
> What does "mc" mean?
> --
> Allan
>
>
> "Don Guillett" wrote:
>
>> I would use a FINDNEXT macro to find all instances. You don't need to
>> look
>> for "summary"
>>
>> Sub findsubtotals()
>> mc = 1 'col A
>> lr = Cells(Rows.Count, mc).End(xlUp).Row
>> 'With Range("a1:a" & lr)
>> With Cells(1, mc).Resize(lr)
>> Set c = .find(What:="subtotal", LookIn:=xlValues, _
>> LookAt:=xlWhole, SearchOrder:=xlByRows, _
>> SearchDirection:=xlNext, MatchCase:=False)
>>
>> If Not c Is Nothing Then
>> firstAddress = c.Address
>> Do
>> MsgBox c.Row
>> c.Offset(, 7).Resize(, 4).Borders.LineStyle = xlContinuous
>> Rows(c.Row).Font.Bold = True
>>
>> Set c = .FindNext(c)
>> Loop While Not c Is Nothing _
>> And c.Address <> firstAddress
>> End If
>> End With
>> End Sub
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "Flipper" <Flipper@discussions.microsoft.com> wrote in message
>> news:0F9E6FD2-ED4B-443C-BE7F-824ABAC7F457@microsoft.com...
>> > Hello
>> >
>> > I would like to write a "Do Until" macro that will search for the word
>> > "Sub-total" and after finding it. move 7 columns over, then select the
>> > next
>> > four cells to the right and apply a certain border to those cells.
>> > After
>> > that, I would like that entire row formatted with Bold. I would want
>> > to
>> > repeat these steps until it comes to the word Summary.
>> >
>> > For example find subtotal (which in this case is in cell B10), then
>> > move
>> > over 7 columns to cell I10, select I10:L10, then apply formatting to
>> > those
>> > cells. after that select row 10 and apply bold format.
>> >
>> > I know this should be easy, but I have never done a looping macro.
>> >
>> > Thanks
>> > --
>> > Allan
>>
>> .
>>

Sorry to bother, The macro as is doesn't do anything in my workbook and I
just don't understand the language. Would it be possible for you to explain
it to me?

Thanks you
--
Allan


"Flipper" wrote:

> What does "mc" mean?
> --
> Allan
>
>
> "Don Guillett" wrote:
>
> > I would use a FINDNEXT macro to find all instances. You don't need to look
> > for "summary"
> >
> > Sub findsubtotals()
> > mc = 1 'col A
> > lr = Cells(Rows.Count, mc).End(xlUp).Row
> > 'With Range("a1:a" & lr)
> > With Cells(1, mc).Resize(lr)
> > Set c = .find(What:="subtotal", LookIn:=xlValues, _
> > LookAt:=xlWhole, SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, MatchCase:=False)
> >
> > If Not c Is Nothing Then
> > firstAddress = c.Address
> > Do
> > MsgBox c.Row
> > c.Offset(, 7).Resize(, 4).Borders.LineStyle = xlContinuous
> > Rows(c.Row).Font.Bold = True
> >
> > Set c = .FindNext(c)
> > Loop While Not c Is Nothing _
> > And c.Address <> firstAddress
> > End If
> > End With
> > End Sub
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguillett@gmail.com
> > "Flipper" <Flipper@discussions.microsoft.com> wrote in message
> > news:0F9E6FD2-ED4B-443C-BE7F-824ABAC7F457@microsoft.com...
> > > Hello
> > >
> > > I would like to write a "Do Until" macro that will search for the word
> > > "Sub-total" and after finding it. move 7 columns over, then select the
> > > next
> > > four cells to the right and apply a certain border to those cells. After
> > > that, I would like that entire row formatted with Bold. I would want to
> > > repeat these steps until it comes to the word Summary.
> > >
> > > For example find subtotal (which in this case is in cell B10), then move
> > > over 7 columns to cell I10, select I10:L10, then apply formatting to those
> > > cells. after that select row 10 and apply bold format.
> > >
> > > I know this should be easy, but I have never done a looping macro.
> > >
> > > Thanks
> > > --
> > > Allan
> >
> > .
> >

Hi Allen,

I think it means my column.

Dan