Problem with ranges

[Brad]

What the macro is trying to do is to look at the text in column A and insert
a page break in-between paragraphs (after the text exceeds a certain length)
......

Currently, the logic is bombing at

Set Cellcheck = shtGPA9D("A55:A176").Range

What am I doing wrong??

Sub test3()
Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim CRow As Integer

Dim rngsht As Range
Dim Cellcheck As Range

MaxHeight = 77

Set rngsht1 = shtGPA9D.Rows("55:176")
Set Cellcheck = shtGPA9D("A55:A176").Range
TotalHeight = 0
PRow = 0
CRow = 0

For Each r In rngsht1
If Cellcheck(r) = " " Then
PRow = CRow + 54
CRow = r + 54
End If

TotalHeight = TotalHeight + r.RowHeight

If TotalHeight > MaxHeight Then
shtGPA9D.Rows(PRow & ":" & PRow + 3).Insert
shtGPA9D.Range("J" & PRow & ":J" & PRow + 57).Value =
shtGPA9D.Range("RightVF1:RightVF4").Value
shtGPA9D.Range("J" & PRow + 54 & ":J" & PRow +
57).HorizontalAlignment = xlRight
shtGPA9D.HPageBreaks.Add before:=Cells(PRow + 4, "a")

shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4)
TotalHeight = 0
End If
Next r
End Sub

 

[Gary''s Student]

Where is:
shtGPA9D
initialized or defined?

 

[JP]

When I pasted your code into an empty module and clicked Debug>Compile
VBAProject, I got a "variable not defined" error for "shtGPA9D".

What is "shtGPA9D" supposed to be?

HTH,
JP

 

[Rick Rothstein \(MVP - VB\)]

Assuming this statement is working for you...

Set rngsht1 = shtGPA9D.Rows("55:176")

You should be able to set Cellcheck like this...

Set Cellcheck = rngsht1.Resize(, 1)

Rick

 

[Brad]

shtGPA9D is the "name" of a particular sheet in the workbook. The name that
I'm referring to is the fist field in VBE properties (under Alphabetic)

 

[JLGWhiz]

You also need to define what " " means. I assumed it was supposed to be ""
for null string. Then you need to look at your If statement. It will not
work in the present configuration. I changed it to use
SpecialCells(xlCellTypeBlanks) < 1 and got past the error message only to
find that your calculation for the Total height will kick in and try to use
the Prow and Crow variables which were not defined because no blank rows were
found. In other words, it needs some work.

 

[Brad]

The following is giving better results - still needs some work... Is there a
better way?? There blank rows are a given and it is inserting the proper
text.

Sub test3()
Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim CRow As Integer
Dim RCnt As Long

Dim rngSHT1 As Range
Dim rngColA As Range

MaxHeight = 700

Set rngColA = shtGPA9D.Range("A55:A176")
TotalHeight = 0
PRow = 0
CRow = 0

For RCnt = 1 To 124
If rngColA(RCnt) = "" Then
PRow = CRow + 54
CRow = RCnt + 54
End If

TotalHeight = TotalHeight + rngColA(RCnt).RowHeight

If TotalHeight > MaxHeight Then
shtGPA9D.Rows(PRow & ":" & PRow + 3).Insert
shtGPA9D.Range("J" & PRow & ":J" & PRow + 3).Value =
shtGPA9D.Range("RightVF1:RightVF4").Value
shtGPA9D.Range("J" & PRow & ":J" & PRow + 3).HorizontalAlignment
= xlRight
shtGPA9D.HPageBreaks.Add before:=Cells(PRow + 4, "a")

shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4)
TotalHeight = 0
End If
Next RCnt
End Sub

 

[JLGWhiz]

Looks like you cleaned up the big ones. Good luck.