Loopinq question

[Brad]

Below is the macro that mostly works - the problem is that when it gets past
a total row height, it should skip five row and start recounting - but it
isn't. What am i doing wrong?

Sub SetPages()

Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim CRow As Integer
Dim r As Range

MaxHeight = 700

Set rngsht1 = shtVarV.Rows("57:192")
TotalHeight = 0
PRow = 57
CRow = 57

For Each r In rngsht1
If IsEmpty(cell) Then
PRow = CRow
CRow = r.Row
End If

TotalHeight = TotalHeight + r.RowHeight
Debug.Print ("total row height " & TotalHeight & " current row " & PRow)
If TotalHeight > MaxHeight Then
shtVarD.Rows(PRow & ":" & PRow + 5).Insert
shtVarD.Range("J" & PRow & ":J" & PRow + 3).Value =
shtVarD.Range("RightVF1:RightVF4").Value
shtVarD.Range("J" & PRow & ":J" & PRow + 3).HorizontalAlignment =
xlRight

shtVarD.Range("A" & PRow + 3).Value = shtVarD.Range("LeftVF4").Value
shtVarD.Range("A" & PRow + 3).HorizontalAlignment = xlLeft
shtVarD.HPageBreaks.Add before:=Cells(PRow + 4, "a")

shtVarD.Range("title1").Copy shtVarD.Range("a" & PRow + 5)

r = PRow + 5
Debug.Print ("total row height " & TotalHeight & " current row " &
PRow)

TotalHeight = 0
End If
Next r
Debug.Print ("total row height " & totheight)
End Sub

 

[Jim Thomlinson]

I assume that this is where you are assuming that you are skipping 5 rows.
r = PRow + 5

but r is a range so what you have is equivalent to
r.Value = PRow + 5

You are setting the value property of the range object. What you have
fundamentally will not work. Your code is moving through each cell in the
range rngsht1. It has no ability to skip any cells in that range.

You need something more like
dim lng as long

for lng = 1 to xx step 5
'...
next lng

 

[Brad]

In a nutshell, what I'm looking for is to count the rowheight of each of the
rows starting with line 57, once the culmuative total row height exceeds 770
insert five particular lines of data and then starting with the fifth line
added start the process all over again. The only restriction was that these
five lines of data cannot be inserted in a paragraph. That is why I want
to have a counter to know where the blank lines are so that when the 770 is
exceed - the program goes back to the last blank line and adds the five lines.

The right information is being inserted, however as you correctly pointed
out I'm not counting the row heights appropriately do to the fact that I'm
using a For each.

What would you recommend? Your previous recommendation would have me
skipping four lines of data that I need have to count.

Should I simply have

For r = 57 to 200
Instead of
For each r in rngsht1

If this is the case - what would be the best way to count the row heights
up? In addition, the ending point will have to be variable - adding multiple
lines may make the last row 203 (for example).

Thanks again for helping, you have always been helpful.

 

[Brad]

Jim,

I'm getting closer but the offset is off one.....

Sub SetPages()

Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim r As Integer
Dim StartCell As Range

Set StartCell = shtVarD.Range("a1")
MaxHeight = 700

TotalHeight = 0
PRow = 55

For r = 55 To 58
If IsEmpty(StartCell.Offset(r, 0)) Then
PRow = r
End If
TotalHeight = TotalHeight + StartCell.Offset(r, 0).RowHeight
Debug.Print (StartCell.Offset(r, 0))
Debug.Print ("total row height " & TotalHeight & " current row " & r & "
PRow " & PRow)

If TotalHeight > MaxHeight Then
shtVarD.Rows(PRow & ":" & PRow + 5).Insert
shtVarD.Range("J" & PRow & ":J" & PRow + 3).Value =
shtVarD.Range("RightVF1:RightVF4").Value
shtVarD.Range("J" & PRow & ":J" & PRow + 3).HorizontalAlignment =
xlRight

shtVarD.Range("A" & PRow + 3).Value = shtVarD.Range("LeftVF4").Value
shtVarD.Range("A" & PRow + 3).HorizontalAlignment = xlLeft
shtVarD.HPageBreaks.Add before:=Cells(PRow + 4, "a")

shtVarD.Range("title1").Copy shtVarD.Range("a" & PRow + 5)
r = PRow + 5
TotalHeight = 0
End If
Next r
Debug.Print ("total row height " & totheight)
End Sub