Basic Loop

[Herman Merman]

I've written a macro that formats an exported report file from a
accounting application (SAP). I want to insert a line between th
various subtotal lines in the report. The macro identifies how man
lines need to be inserted (this will vary with each report run) but
don't know how to get it to

(A) - Insert the rows between subtotal lines
(B) - Get the macro to stop inserting rows at the right time.

I guess a basic FOR NEXT loop is required but the help index isn't
lot of help with getting the syntax right and I haven't done loop
before.

Appreciate your hel

 

[Frank Kabel]

Hi
try the following macro. It tests column A and inserts a blank row if
the word 'Subtotal' is found
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If InStr(Cells(row_index, "A").Value,"Subtotal")>0 then
Cells(row_index + 1, "A").EntireRow.Insert (xlShiftDown)
End If
Next
End Sub

 

[Hardy]

Herman,

I'm not sure that you have been clear enough. For the first part (t
insert line), just use Selection.EntireRow.Insert at the appropriat
point.

But, to help further, need to know,

1. Are there several sub-total lines in each report
2. Does the space vary between subtotal line
3. How many blank lines do you need between lines
4. IS there always something below subtotal lines, even the last one.
5. Will you always be adding lines, i.e. could it be that you need t
subtract blank lines to achieve correct amount of blank lines

 

[Herman Merman]

Hi Hardy, I've managed to solve this problem using a Do..Loop statement

counter = 0
myNum = SUBTOTALS (Previously specified number of subtotal rows
determined by "activecell.value" formula)
Do
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("a1").Select
Selection.Entirerow.Insert
myNum = myNum - 1
counter = counter + 1
Loop While myNum > 0

End Sub

Frank also came up with a great looking solution which I will try out as
well, but I would also be interested in your idea as it's great to have
alternative methods of doing these things. To answer your questions though:

1. Yes there are several subtotal lines in each report, each report can
vary anywhere from 1 to 100. Essentially what I've done already in the
Macro is identify the subtotals with a placeholder formula, eg
=if(left(B1,5)="Total",1,""). The sum of all the formula's returning "1" is
labelled SUBTOTALS through the activecell.value function, this is the value
that tells the myNum function how many times to loop

2. The space does vary between subtotal lines as there can be any number of
rows between them. The report I'm looking at subtotals different projects
in a programme of work, more projects can be added to a programme at any
time.

3. Just need one blank line between lines
4. Yes there is always something below the subtotal lines, except the last
one
5. The report comes formatted already with spacing, an earlier part of the
Macro removes the blank lines in order to better de-limit the data and
remove other unnecessary or unwanted information/characters. I tried
putting something in which kept the original blanks but it kept mucking
parsing the project data so it had to go.

I'm not really happy with the solution I figured out above as I don't trust
the "Selection.End(xlDown).Select" function. If you've got something a bit
less primative I'd love to give it a shot.

Cheers

Herman