Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loopinq question
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 befo=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loopinq question
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 befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loopinq question
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. -- 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 befo=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loopinq question
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 befo=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 befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|