Question for Jim Thomlinson or Ron de Bruin

[Brad]

Both of you gentlemen have given great advice

I have a posting in Excel Programming
"Detailed formatting problem" dated on 5/30

One solution was to put the Do...While in a "For i = 1 to 2" and it works
but is this the best solution?

Below was the maco that needs work and yes I will be using range names on
the macro when

Sub ReformatSentences()
Dim RemainingBold As Integer
Dim SecondSentence As Integer
Dim LastSentence As Integer
Dim rowcnt As Integer

shtPrem.Range("o13:s22").Clear
shtPrem.Range("b13:b18").Copy
shtPrem.Range("o1318").PasteSpecial xlValues

shtPrem.Range("o13:s22").Justify
shtPrem.Range("o13:s22").Font.Bold = False
rowcnt = 12 + shtPrem.Range("o11").Value
RemainingBold = 158
Do
LastSentence = Len(shtPrem.Range("o" & rowcnt))
If LastSentence <= RemainingBold Then
shtPrem.Range("o" & rowcnt).Font.Bold = True
Else
shtPrem.Range("o" & rowcnt).Characters(LastSentence -
RemainingBold, 158).Font.Bold = True
End If
RemainingBold = RemainingBold - LastSentence
rowcnt = rowcnt - 1
Loop While RemainingBold > 0
End Sub

 

[Jim Thomlinson]

Hard to comment without knowing what the sub is supposed to do. Does it work
correctly. If not what exactly is the problem? From a purely syntax point of
view there are a couple of things you could do to make this more efficient.

1. use long instead of integer. behind the scenes the system is converting
integers to long and then back again. It has to do with running a 32 bit OS
and working with 16 bit data types.

2. Use a with statement. That speeds up code by not haing to get a new
handle to the object each time you want to do something to it.

3. No point in doing a paste values. just set the values equal...

Sub ReformatSentences()
Dim RemainingBold As Long
Dim SecondSentence As Long
Dim LastSentence As Long
Dim rowcnt As Long

With shtPrem
.Range("o13:s22").Clear
.Range("o1318").Value = .Range("b13:b18").Value

.Range("o13:s22").Justify
.Range("o13:s22").Font.Bold = False
rowcnt = 12 + .Range("o11").Value
RemainingBold = 158
Do
LastSentence = Len(.Range("o" & rowcnt))
If LastSentence <= RemainingBold Then
.Range("o" & rowcnt).Font.Bold = True
Else
.Range("o" & rowcnt).Characters(LastSentence - RemainingBold,
158).Font.Bold = True
End If
RemainingBold = RemainingBold - LastSentence
rowcnt = rowcnt - 1
Loop While RemainingBold > 0
End With
End Sub

 

[Brad]

First of all thank you for look at this.

What the sub is doing is formatting 6 sentences in paragraph form. The
first sentence has variable length. The first sentence is a formula where I
have text then concatenating premium amount, adding more text then
concatenating billing schedule.

The next 4 sentences are static (not changing) and the last sentence has to
be bolded. However, when using the "fill-justify" logic bolding of the last
sentence is lost. When I rebold the last sentence - it exceeds the right
margin. This is the problem.

If the I force the Do-Loop a second time it then works, I was hoping you
might be able to provide a better answer

Does this make sense - or is it as clear as mud?