RE: Loopinq question

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
--
Wag more, bark less


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Brad" wrote:


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



.

Quantcast