I am trying to insert columns between positive and negative

[DevintheDude]

I want to search a column from - to 0 to + and I want to insert columns between the - and + or if their is 0's insert columns between 0 and +

What kind of method formulas can do this

 

[HalfAce]

Hello DevintheDude,
I'm assuming you're looking for a vba solution as there is no way to achieve this with
any kind of formula(s)

I'm going to need a bit of clarification before being able to provide a meaningful
example.

I want to search a column from - to 0 to + and I want to insert columns between the - and + or if their is 0's insert columns between 0 and +

You want to:
Search a column & insert one or more row(s)? or
Search a row & insert one or more column(s)?

While searching, say it finds a negative number and then a positive number:
(Do the insert?)
Or, say it finds a zero and then a negative number. . . (?)
Or, . . . a negative, a zero, then a negative. . . ?

Can you narrow this down a bit?

 

[DevintheDude]

I want to insert 6 rows between the negative and positive numbers and if it has 0's then insert the rows between 0 and positive numbers example
-5
-4
-3
6 spaces
1
2
3


or......
-5
-4
0
0
6 spaces
1
2
3

thank you for help...

 

[HalfAce]

Something like this perhaps?

Sub InsertRowsDemo()
Dim LstRw&, iRng As Range, i As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
If LstRw = 1 Then MsgBox ("No Data to work with."), , "No Data": Exit Sub
Set iRng = Range("A1:A" & LstRw)
Application.ScreenUpdating = False
For Each i In iRng
  If Not IsEmpty(i) Then
    If i < 1 And i(2) > 0 Then
      i(2).Resize(6).EntireRow.Insert
    End If
  End If
Next
Application.ScreenUpdating = True
End Sub

Hope it helps.

 

[DevintheDude]

ADVERTISEMENT

Thanks it helps a little bit i think i can work with this to make it work for me it is inserting 6 rows between some of the zeros and some of the positive numbers

Thanks for help and the great start

 

[HalfAce]

it is inserting 6 rows between some of the zeros and some of the positive numbers

Does this mean it's inserting between zero and zero, and between
some of the (consecutive) positive numbers?
It shouldn't be.
It's told to test if a cell is less than zero AND if the next cell is greater than zero.

Are the values all whole numbers? - or will some of them be (say) 0.4?
0.4 would meet the criteria of "the next cell" being greater than zero while (depending on
formatting & such), it could be displaying a zero.

 

[DevintheDude]

ADVERTISEMENT

yeah some of them are .#######

 

[HalfAce]

OK. How about if you change this line:
If i < 1 And i(2) > 0 Then
to:
If i < 0.00000001 And i(2) > 0 Then

(Don't really have time to do exhaustive testing right now.
Will check back in the morning.)

Hope it helps.

 

[DevintheDude]

This code does the job

Sub InsertRowsDemo()
Dim lastrow, row
lastrow = Cells(Rows.Count, "A").End(xlUp).row
If lastrow = 3 Then MsgBox ("No Data to work with."), , "No Data": Exit Sub
'Set iRng = Range("P4:P" & LstRw)
Application.ScreenUpdating = False
Stop
Do Until Cells(lastrow, "P").Value < 0.5
lastrow = lastrow - 1
Loop

Stop
For row = lastrow To lastrow + 5 Step 1
Rows(row + 1).EntireRow.Insert
Next
Application.ScreenUpdating = True

'For row = lastrow To 4 Step -1
' If Not IsEmpty(row) Then
' If Cells(row, "P") > 0.5 Then Rows(row - 1).EntireRow.Insert
' End If
'Next
End Sub

 

[HalfAce]

That last code doesn't (completely) work for me with the sample data you provided but
if it works on your real data that's all that counts.

Good job getting it figured out.