insert row when sum of values equals 100

L

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!
 
L

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
 
J

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
 
L

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?
 

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