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

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

"Tesla5000" wrote:

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

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
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Tesla5000" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tesla5000" wrote in message
...
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

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

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.

"Tesla5000" wrote:

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

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.




"Tesla5000" wrote:

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

Ahaa, I knew I had done something strange. Thanks for the explanation!

Tes

"JLGWhiz" wrote:

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.




"Tesla5000" wrote:

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