Adding rows based if cell value is 1 or 0

[hbuzz]

Hi there,

I am trying to add rows depending on a cell value in a column. Basically I have a column with either 1 or 0 as the value. I would like a to add a row where each cell value is 1. I have searched the forum and have found this from another post with a similar problem however I have only a basic understanding of vba see code below.

The problem that I am trying to solve is adding up column cell values and I would like to split the data by row when a whole number of 1 reached ie.

0.5
0.25
0.25
(Add row here)
1
(Add row here)
0.5
0.5
(Add row here)
1
(Add row here) etc

I have tried to make things simpler by adding up these up as running totals (ie putting a value in G2 and then =(G2+C3) and then creating the 1's and 0's on whole numbers ie (=if(G2=INT(G2),"1","0"). If there's an answer to split the row either way I would be very happy for any help. Thanks!




sub AddBlankRows()
'
dim iRow as integer
range("a1").select
'
irow=1
'
do
'
if cells(irow+1, 1)<>cells(irow,1) then
cells(irow+1,1).entirerow.insert shift:=xldown
irow=irow+2
else
irow=irow+1
end if
'
loop while not cells (irow,1).text=""
'
end sub

[JOHN H. DAVIS]

Please use code tags when posting. This assumes the Column is A.

Please Login or Register  to view this content.

[millz]

@John, I think you will need to make some amendments. OP wants to insert row once values sum up to 1, e.g. 0.5 + 0.25 + 0.25

[hbuzz]

Hi John,

Thanks for your help, seems really close now however it seems to be skipping a line when a row is added and not taking the next line down into account?


1 132 BBHI7100 1 A LOAD 1 whole Number

1 132 BLCLN205 1 A LOAD 2 whole Number
1 132 BLCLN211 1 A LOAD 3 whole Number

1 132 BLTMC405 1 A LOAD 4 whole Number

1 132 BLTMC410 1 A LOAD 5 whole Number
1 132 BLTMC525 1 A LOAD 6 whole Number

1 192 BLCLN205 1 A LOAD 7 whole Number

0 194 BLCLN205 0.25 SPLIT LOAD 7.25 not whole number
0 194 BLCLN590 0.25 SPLIT LOAD 7.5 not whole number
1 194 BLCLN240 0.5 SPLIT LOAD 8 whole Number
1 194 BLCLN211 1 A LOAD 9 whole Number

1 194 BLCLN227 1 A LOAD 10 whole Number

1 194 BLTMC500 1 A LOAD 11 whole Number
1 194 BLTMC501 1 A LOAD 12 whole Number

1 3657 BLCLN205 1 A LOAD 13 whole Number

0 3660 BLCLN220 0.5 SPLIT LOAD 13.5 not whole number
1 3660 BLCLN590 0.5 SPLIT LOAD 14 whole Number

1 3660 BLCLN205 1 A LOAD 15 whole Number

0 3660 BLTMC500 0.5 SPLIT LOAD 15.5 not whole number
1 3660 BLTMC501 0.5 SPLIT LOAD 16 whole Number
1 4336 BLCLN207 1 A LOAD 17 whole Number

1 4336 BLCLN211 1 A LOAD 18 whole Number

0 4336 BLTMC405 0.5 SPLIT LOAD 18.5 not whole number
1 4336 BLTMC408 0.5 SPLIT LOAD 19 whole Number
0 4336 BLTMC474 0.5 SPLIT LOAD 19.5 not whole number

1 4336 BLTMC475 0.5 SPLIT LOAD 20 whole Number



Thanks Haydn

[JOHN H. DAVIS]

@John, I think you will need to make some amendments. OP wants to insert row once values sum up to 1, e.g. 0.5 + 0.25 + 0.25

You're right I completely misread the thread requirements.

[JOHN H. DAVIS]

However, the new data is different? Maybe:

Please Login or Register  to view this content.

[hbuzz]

Hi John,

That's fantastic and works perfectly. Thank you very much for your help with this one. Don't think I'd have ever got there by myself!

Thanks Again!
Haydn

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.


9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.