marco that inserts rows?
 

Hi,
I have a worksheet with a column that has either a M, P or X in it. When
ever there is a M followed by a P i would like to insert a new row between
the two.
Any suggests or help would be great!
Cheers

marco that inserts rows?
 

You could use a macro to accomplish that. Change the sheet and range
reference as needed.

Sub test()
Dim rngData As Range
Dim rngInsert As Range
Dim rngCell As Range

Set rngData = Sheets("Sheet1").Range("A1:A10")

For Each rngCell In rngData.Cells
If UCase(rngCell.Value) = "M" And _
UCase(rngCell(2, 1).Value) = "P" Then
If rngInsert Is Nothing Then
Set rngInsert = rngCell(2, 1)
Else
Set rngInsert = Union(rngInsert, _
rngCell(2, 1))
End If
End If
Next rngCell

If Not rngInsert Is Nothing Then _
rngInsert.EntireRow.Insert

End Sub


"lloydyleg11" wrote:

Hi,
I have a worksheet with a column that has either a M, P or X in it. When
ever there is a M followed by a P i would like to insert a new row between
the two.
Any suggests or help would be great!
Cheers

marco that inserts rows?
 

JMB your are a champion!! It worked a treat!

....but it also exposed another dilema. I also needed to insert a row between
some of the P's, when the sum of another column =100. eg:
M 100
M 100
(blank inserted by your macro)
P 90
P 10
P 50 (need a blank inserted above this line)
P 50
M 100

I have no idea how this can be done, but any help would be greatly
appreciated! Its going save me from major headaches!!
Cheers
lloydy

marco that inserts rows?
 

I apologize for not responding sooner. Out of town at a friend's and his
computer errored out when trying to get to MS's newsgroups.

This macro will go through the specified range and keep a running total of
the column to the right for all cells w/ a "P". Then once the running total
is = 100, will insert a row just below the cell that pushed the total = 100
(if that cell's row is not already empty) and reset the running total to 0
(start the count over).

I am assuming you've already got the needed rows inserted to separate M's
and P's so I did not fold this issue into the previous macro.

Be sure to backup. If the column w/the numbers is not one column to the
right, you will need to adjust this line
lngCount = lngCount + rngCell(1, 2).Value
where rngCell is a range variable that will refer to the cells in the range
specified (A1:A14 in the code below - think of it as a placeholder as the
code loops through the range). rngCell will always be (1, 1) (Row, Column) -
so the cell just to the right is rngCell(1, 2). Adjust as needed to refer to
the proper column containing the numeric data.

I think I've made some assumptions about what you want, so post back (with
more details) if it does not work.


Sub test2()
Dim rngData As Range
Dim rngInsert As Range
Dim rngCell As Range
Dim lngCount As Long

Set rngData = Sheets("Sheet1").Range("A1:A14")

For Each rngCell In rngData.Cells
If UCase(rngCell.Value) = "P" Then
lngCount = lngCount + rngCell(1, 2).Value
If lngCount = 100 Then
lngCount = 0
If Application.CountA(rngCell(2, 1).EntireRow) 0 Then
If rngInsert Is Nothing Then
Set rngInsert = rngCell(2, 1)
Else
Set rngInsert = Union(rngInsert, _
rngCell(2, 1))
End If
End If
End If
End If
Next rngCell

If Not rngInsert Is Nothing Then _
rngInsert.EntireRow.Insert

End Sub


"lloydyleg11" wrote:

JMB your are a champion!! It worked a treat!

...but it also exposed another dilema. I also needed to insert a row between
some of the P's, when the sum of another column =100. eg:
M 100
M 100
(blank inserted by your macro)
P 90
P 10
P 50 (need a blank inserted above this line)
P 50
M 100

I have no idea how this can be done, but any help would be greatly
appreciated! Its going save me from major headaches!!
Cheers
lloydy