I am trying to insert columns between positive and negative

DevintheDude

Board Regular
Joined
Jun 8, 2007
Messages
153
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

Board Regular
Joined
Jun 8, 2007
Messages
153
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Something like this perhaps?
Code:
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

Board Regular
Joined
Jun 8, 2007
Messages
153

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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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. :unsure:
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

Board Regular
Joined
Jun 8, 2007
Messages
153

ADVERTISEMENT

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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

Board Regular
Joined
Jun 8, 2007
Messages
153
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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. :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top