| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with ranges
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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
Where is:
shtGPA9D initialized or defined? -- Gary''s Student - gsnu200773 "Brad" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
When I pasted your code into an empty module and clicked DebugCompile
VBAProject, I got a "variable not defined" error for "shtGPA9D". What is "shtGPA9D" supposed to be? HTH, JP On Mar 11, 2:34*pm, Brad wrote: 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 befo=Cells(PRow + 4, "a") * * * * * * shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) * * * * * * TotalHeight = 0 * * * * End If * * Next r End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
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" wrote in message ... 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
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) "Brad" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
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" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next RCnt End Sub "JLGWhiz" wrote: 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" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Problem with ranges
Looks like you cleaned up the big ones. Good luck.
"Brad" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next RCnt End Sub "JLGWhiz" wrote: 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" wrote: 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 befo=Cells(PRow + 4, "a") shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4) TotalHeight = 0 End If Next r End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Worksheets v Ranges 'Again' | Excel Programming | |||
| Problem with named ranges | Excel Programming | |||
| Problem with dynamic ranges | Excel Worksheet Functions | |||
| Problem with code about ranges | Excel Programming | |||
| PRoblem with Ranges and Sumproduct under VBA | Excel Programming | |||
Linear Mode
