insert row when sum of values equals 100

[lloydyleg11]

Hi,

I would like to insert a blank row when the sum of the values in a column of
cells equals 100.

Before:
1
2 10
3 90
4
5
6 50
7 50
8
9 10
10 10
11 80
12

After:
1
2 10
3 90
*inserted blank row*
4
5
6 50
7 50
*inserted blank row*
8
9 10
10 10
11 80
*inserted blank row*
12

Hope this makes sense!

The first column i entered was the row numbers. the second the values in the
cells. There are not always blanks after the sum equals 100, sometimes the
values keep going.

The after case what i would like it to look like after running a macro or
something. the blanks in the row numbers are the places i would like to
insert rows, after the sum of 100 is reached. The values in the second column
will always add to 100...

Thanks in advance!

 

[lloydyleg11]

hi jacob,

thanks for the quick reply!

i copy and pasted the macro into a new module and moved the column with the
values into column A.

when i ran the macro it came up with a debugging message and this line was
higlighted:
intTotal = intTotal + Range("A" & lngRow)

any ideas what i should do?

cheers,
lloydy

 

[Jacob Skaria]

I have tried it with values 100,80,20,50,50,90,10 in ColA. cells 1 to 7.

If you have values in Col B replace all "A" the macro to "B"


If this post helps click Yes

 

[lloydyleg11]

Hey Jacob,

I copied the column into a new worksheet and ran the macro. it ran without
the debugging issue so it must have been something in my old worksheet.

It still didn't work out exactly as I would have liked though... it inserted
the rows before rather than after the total equalled 100, and it didn't
recognise when there were sequential numbers summing to 100. There are some
blank cells in the column, do you think could be causing the issues?