Do Until Macro

[Flipper]

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

 

[Don Guillett]

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

 

[Flipper]

What does "mc" mean?
--
Allan

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
(e-mail address removed)


.

 

[Don Guillett]

Think about it
mc = 1 'col A

 

[Flipper]

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

 

[dan dungan]

Hi Allen,

I think it means my column.

Dan

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.