Databases
StackOverflow2013
Postsdbo
POFor...Next with expanding range in Excel
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFor...Next with expanding range in Excel
    primarykey
    data
    text
    <p>I have a range of formulae in cells D5:D103 of a report, which link to another sheet in the book. Columns A:C contain text results, Column D has $ amounts as the results. A4:D4 contains headings for the data, and A1:D3 is a general heading to describe the report. The location of the last row will change each month, could be >103, could be &lt;103.</p> <p>After every 16 rows of data I need to insert 3 rows by 4 columns pasted from a named range in the sheet, and insert a total of the 16 rows in column D of the 2nd of the 3 rows. I then add a page break below ( a crude pagination exercise dictated by other requirements).</p> <p>The following code achieves most of this:-</p> <pre><code>Dim Formula1 As String Formula1 = "=SUM(R[-17]C:R[-2]C)" For i = 21 To (Range("a5", Range("d4").End(xlDown)).Rows.Count + 4) Step 19 Range("Total_credit").Select Selection.Copy Range("a" &amp; i).Select Selection.Insert Shift:=xlDown Range("a" &amp; i).Offset(1, 3) = Formula1 Selection.Offset(3).PageBreak = xlPageBreakManual Next i </code></pre> <p>However, I am left with a block of 19 rows of data at the bottom, which should be 2 blocks of 16 with the pasted text and a total below each (ideally there would be no blank rows in the last sequence, but I can live with that if I can get the rest right). The same issue seems to occur when I make the block of data a few rows bigger i.e. the last total will always be in the same place (D117).</p> <p>It appears the value of i is incrementing each loop, but this doesn't seem to be reflected in the process. I've tried all sorts of things and searched thoroughly to no avail, and my head is just spinning now. I'm sure someone will see instantly where I've gone wrong, it seems like it should be a simple fix.</p> <p>Thanks in advance.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USKiwi Graham
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFor...Next with expanding range in Excel
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhen you find your last row using .End(xlDown), that's the initial end. Your table gets added in between every set of data, but your "last row" doesnt change, so your loop stops prematurely.
      singulars
      1. POFor...Next with expanding range in Excel
      1. USJoe Laviano
    2. COThanks, Joe, that confirms what I thought is the issue. Is there a way to fix this still using this method, or do I need to try something else? I had tried re-calculating "i" before "Next i" to no effect. Another way might be to increment i by 3 each time the loop runs, or to calculate the number of times the loop needs to run (count of rows/16, rounded up). But I can't figure how to make either of those work with my available time. Any help greatly appreciated.
      singulars
      1. POFor...Next with expanding range in Excel
      1. USKiwi Graham
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload