insert row when sum of values equals 100

Hi,

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

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

Try this macro, I have assumed you have the values in ColA. Please change as
required. If you are new to macros; set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro

Sub InsertBlanksAfter100()

Dim intTotal As Integer
Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
If intTotal = 100 Then
ActiveSheet.Rows(lngRow + 1).Insert
intTotal = 0
End If
intTotal = intTotal + Range("A" & lngRow)
Next
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"lloydyleg11" wrote:

Hi,

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

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

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" wrote:

Try this macro, I have assumed you have the values in ColA. Please change as
required. If you are new to macros; set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro

Sub InsertBlanksAfter100()

Dim intTotal As Integer
Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
If intTotal = 100 Then
ActiveSheet.Rows(lngRow + 1).Insert
intTotal = 0
End If
intTotal = intTotal + Range("A" & lngRow)
Next
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"lloydyleg11" wrote:

Hi,

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

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

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
---------------
Jacob Skaria


"lloydyleg11" wrote:

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" wrote:

Try this macro, I have assumed you have the values in ColA. Please change as
required. If you are new to macros; set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro

Sub InsertBlanksAfter100()

Dim intTotal As Integer
Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
If intTotal = 100 Then
ActiveSheet.Rows(lngRow + 1).Insert
intTotal = 0
End If
intTotal = intTotal + Range("A" & lngRow)
Next
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"lloydyleg11" wrote:

Hi,

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

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

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?