macro to add row in same worksheet based on 2 fields not having a

A

aileen

I have 6 columns of data and an unspecified number of rows. If a row has a
number that is not 0 in both columns 5 & 6 then I need to insert a row below
that row and copy all the data from the original row. Then I need to put a 0
in the original row column 5 and put a 0 in the inserted row column 6. e.g.
SPX OCT 2008 900 0 400(this row would be left alone) but SPX OCT 2008 1000
-200 300(this row would need the macro applied to it). Is this possible?
 
J

Joel

Sub addrows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("E" & RowCount) = 0 And _
Range("F" & RowCount) = 0 Then

Rows(RowCount).Copy
Rows(RowCount + 1).Insert Shift:=xlDown
Range("E" & RowCount) = 0
Range("F" & (RowCount + 1)) = 0
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Loop
 
A

aileen

Thanks Joel. It's very close to working but I need this part " If Range("E"
& RowCount) = 0 And _
Range("F" & RowCount) = 0 Then" to be greater than or less than 0, not
= 0. In other words, any number that isn't 0 whether it's a positive or
negative number. Again, thanks a ton for the help.
 
A

aileen

Just figured it out. Thanks again for the help.

aileen said:
Thanks Joel. It's very close to working but I need this part " If Range("E"
& RowCount) = 0 And _
Range("F" & RowCount) = 0 Then" to be greater than or less than 0, not
= 0. In other words, any number that isn't 0 whether it's a positive or
negative number. Again, thanks a ton for the help.
 

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