Insert Multiple Rows Trouble

T

Tesla5000

Hi,

I am new to VBA and just finished my first program which is working well but
I have a question about part of it. At one point in the process I have the
macro search out a cell with a certain value and then I want it to insert 21
rows after that cell. For some reason in order to get 21 rows I have to run
the loop 42 times. Below is how I did it. It inserts 21 rows. Does anyone
know why I have to loop twice as many times as I think I should. Also I am
sure there is a more efficient way to do this and would welcome learning a
quicker way to acheive the same goal.

Thanks for your help.

' Insert 21 rows after NOI
i = 0
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = "Net Operating Income" Then
Cell.Select
For i = 1 To 42
Rows(ActiveCell(2).Row).Insert
i = i + 1
Next i
End If
Next Cell
 
J

JLGWhiz

Try it this way:

Insert 21 rows after NOI

For Each Cell In ActiveSheet.UsedRange
If Cell.Value = "Net Operating Income" Then
Cell.Offset(1, 0).Resize(21, 1).EntireRow.Insert
End If
Next Cell
 
M

Michael

Try this:
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = "Net Operating Income" Then
Cell.Select
Startingrow = ActiveCell.Row
Endingrow = Startingrow + 21
Rows(Startingrow + 1 & ":" & Endingrow).Insert Shift:=xlDown

End If
Next Cell
 
D

Don Guillett

Use this.
Sub insertRowsif()
For Each cell In ActiveSheet.UsedRange
If cell.Value = "Net Operating Income" Then
Rows(cell.Row + 1 & ":" & cell.Row + 21).Insert
End If
Next cell
End Sub
 
T

Tesla5000

Thanks guys. All three of those are much better than what I did. I knew I
was doing it the hard way.

Does anyone know why I had to loop it 42 times to get 21 rows? I am just
wondering about how it is working should I need a similar construct in the
future.

Thanks again,

Tesla
 
J

JLGWhiz

The way your interior For next loop was written, you were checking each cell
of the UsedRange, which runs from left to right and down. So apparently you
had 21 columns before it found the cell with the criteria. If you had
defined the range, either horizontally or vertically to locate the cell, then
keep the action oriented in that same attitude (vertical or horizontal) then
you would have only needed the 21 to get your row count for insertion.
Understanding how the For...Each loop works will help a lot in how you set it
up and how you define the range. Use the VBA help files for reference as
much as possible. They can save you a lot of grief. You can access them in
the VB editor.
 
J

JLGWhiz

Disregard that last explanation, after taking another look, after a second
look, it is your interior loop that is the problem. You do not neet the i =
i + 1 statement. The For...Next loop automatically increments i by one on
each iteration, so with the statement included, it was incrementing by two
and you would need to double the target number to get the right results.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top