find number & insert rows
 

I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may occur
several times in a column. I want to find the second instance and insert
whatever number of rows necassary (it will vary) to row 25. Then find the
next number 1 and insert rows to 50 and so on.

Any help appreciated.

Steve wallis

find number & insert rows
 

Increase by 25 rows each time? And do you know, for instance, that the
second occurrence of the "1" will be found in a row that is less than 25?
And so on for all subsequent occurrences? Without a doubt? HATH Otto
"Steve Wallis" wrote in message
...
I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may occur
several times in a column. I want to find the second instance and insert
whatever number of rows necassary (it will vary) to row 25. Then find the
next number 1 and insert rows to 50 and so on.

Any help appreciated.

Steve wallis

find number & insert rows
 

Yes. Without a doubt.

Steve

"Otto Moehrbach" wrote in message
...
Increase by 25 rows each time? And do you know, for instance, that the
second occurrence of the "1" will be found in a row that is less than 25?
And so on for all subsequent occurrences? Without a doubt? HATH Otto
"Steve Wallis" wrote in message
...
I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may occur
several times in a column. I want to find the second instance and insert
whatever number of rows necassary (it will vary) to row 25. Then find the
next number 1 and insert rows to 50 and so on.

Any help appreciated.

Steve wallis

find number & insert rows
 

Steve

Here is the first shot at it. I assumed your data was in Column
A starting in A1. I also assumed that when you said:

". I want to find the second instance and insert whatever number of rows
necessary (it will vary) to row 25. Then find the next number 1 and insert
rows to 50 and so on."
you meant that you wanted rows 25, 50, etc to be the last blank row in that
group of blank rows. Is that right? Watch out for line wrap in this
message. I'm also sending you, direct via email, a small file with this
macro properly placed. HTH Otto

Sub RowsAfter1()
Dim RngColA As Range
Dim c As Long
Dim FoundCell As Range
Dim FirstFoundCellRow As Long
c = 0
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set FoundCell = RngColA.Find(What:=Range("B1").Value,
After:=RngColA(RngColA.Count), _
LookAt:=xlWhole)
FirstFoundCellRow = FoundCell.Row
Do
c = c + 1
Set FoundCell = RngColA.Find(What:=Range("B1").Value, _
After:=FoundCell, LookAt:=xlWhole)
If FoundCell.Row = FirstFoundCellRow Then Exit Sub
FoundCell.Offset(1).Resize((25 * c) - FoundCell.Row).Insert
Shift:=xlDown
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Loop
End Sub

"Steve Wallis" wrote in message
...
Yes. Without a doubt.

Steve

"Otto Moehrbach" wrote in message
...
Increase by 25 rows each time? And do you know, for instance, that the
second occurrence of the "1" will be found in a row that is less than 25?
And so on for all subsequent occurrences? Without a doubt? HATH Otto
"Steve Wallis" wrote in message
...
I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may occur
several times in a column. I want to find the second instance and insert
whatever number of rows necassary (it will vary) to row 25. Then find the
next number 1 and insert rows to 50 and so on.

Any help appreciated.

Steve wallis

find number & insert rows
 

Steve
The email address displayed in your last post is not a valid address.
If you want the small file I mentioned, send me a valid email address. My
email address is . Remove the "nop" from this address.
Otto
"Otto Moehrbach" wrote in message
...
Steve

Here is the first shot at it. I assumed your data was in
Column A starting in A1. I also assumed that when you said:

". I want to find the second instance and insert whatever number of rows
necessary (it will vary) to row 25. Then find the next number 1 and insert
rows to 50 and so on."
you meant that you wanted rows 25, 50, etc to be the last blank row in
that group of blank rows. Is that right? Watch out for line wrap in
this message. I'm also sending you, direct via email, a small file with
this macro properly placed. HTH Otto

Sub RowsAfter1()
Dim RngColA As Range
Dim c As Long
Dim FoundCell As Range
Dim FirstFoundCellRow As Long
c = 0
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Set FoundCell = RngColA.Find(What:=Range("B1").Value,
After:=RngColA(RngColA.Count), _
LookAt:=xlWhole)
FirstFoundCellRow = FoundCell.Row
Do
c = c + 1
Set FoundCell = RngColA.Find(What:=Range("B1").Value, _
After:=FoundCell, LookAt:=xlWhole)
If FoundCell.Row = FirstFoundCellRow Then Exit Sub
FoundCell.Offset(1).Resize((25 * c) - FoundCell.Row).Insert
Shift:=xlDown
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
Loop
End Sub

"Steve Wallis" wrote in message
...
Yes. Without a doubt.

Steve

"Otto Moehrbach" wrote in message
...
Increase by 25 rows each time? And do you know, for instance, that the
second occurrence of the "1" will be found in a row that is less than
25? And so on for all subsequent occurrences? Without a doubt? HATH
Otto
"Steve Wallis" wrote in message
...
I would like to find a repeating number in a column and, except for the
first instance, insert rows to a row number. Specifically, no 1 may
occur several times in a column. I want to find the second instance and
insert whatever number of rows necassary (it will vary) to row 25. Then
find the next number 1 and insert rows to 50 and so on.

Any help appreciated.

Steve wallis