If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
insert row when sum of values equals 100
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! |
#3
|
|||
|
|||
insert row when sum of values equals 100
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! |
#4
|
|||
|
|||
insert row when sum of values equals 100
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! |
#5
|
|||
|
|||
insert row when sum of values equals 100
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? |
Thread Tools | |
Display Modes | |
|
|