Macro to add total between changes in values

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Hoping somebody could help me with a macro to take column A and for every change in value add some totals below the last occurence? This is just a small set of the data which could be up to 5000 rows. The values using the sumif (0111,0121,0131,0200) are always constant.

Before
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>E</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Region</TD><TD style="TEXT-ALIGN: center">Employee</TD><TD style="TEXT-ALIGN: center">Current</TD><TD style="TEXT-ALIGN: center">FY09</TD><TD style="TEXT-ALIGN: center">FY10</TD><TD style="TEXT-ALIGN: center">FY11</TD><TD style="TEXT-ALIGN: center">FY12</TD><TD style="TEXT-ALIGN: center">FY13</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>AA</TD><TD style="TEXT-ALIGN: right">0111</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>AA</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0111</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0131</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0200</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>AC</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

After
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>E</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Region</TD><TD style="TEXT-ALIGN: center">Employee</TD><TD style="TEXT-ALIGN: center">Current</TD><TD style="TEXT-ALIGN: center">FY09</TD><TD style="TEXT-ALIGN: center">FY10</TD><TD style="TEXT-ALIGN: center">FY11</TD><TD style="TEXT-ALIGN: center">FY12</TD><TD style="TEXT-ALIGN: center">FY13</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>AA</TD><TD style="TEXT-ALIGN: right">0111</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>AA</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0111</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0121</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0131</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0200</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0111</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0131</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>AB</TD><TD style="TEXT-ALIGN: right">0200</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0111</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0121</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0131</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0200</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>AC</TD><TD style="TEXT-ALIGN: right">0121</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0111</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0121</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0131</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">0200</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I5</TD><TD>=SUMIF($E$2:$E$3,$H5,I$2:I$3)</TD></TR><TR><TD>I9</TD><TD>=SUM(I5:I8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

try

Code:
Sub Macro3()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & MY_ROWS).Value <> Range("A" & MY_ROWS + 1).Value Then
            Rows(MY_ROWS + 1).Resize(7).Insert
        End If
    Next MY_ROWS
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
        If IsEmpty(Range("A" & MY_ROWS).Value) And Not (IsEmpty(Range("A" & MY_ROWS - 1).Value)) Then
            Range("H" & MY_ROWS + 1).Value = "0111"
            Range("H" & MY_ROWS + 2).Value = "0121"
            Range("H" & MY_ROWS + 3).Value = "0131"
            Range("H" & MY_ROWS + 4).Value = "0200"
            Range("H" & MY_ROWS + 5).Value = "Total"
            If IsEmpty(Range("E" & MY_ROWS).Offset(-1, 0).Value) Then
                MY_END_ROW = MY_ROWS
            Else
                MY_END_ROW = Range("E" & MY_ROWS - 1).End(xlUp).Row
            End If
            If MY_END_ROW = 1 Then MY_END_ROW = 2
            Range("I" & MY_ROWS + 1).Formula = _
                "=SUMIF($E$" & MY_ROWS & ":$E$" & MY_END_ROW - 1 & ",$H" & MY_ROWS + 1 & ",I$" & MY_ROWS - 1 & ":I$" & MY_END_ROW - 1 & ")"
            Range("I" & MY_ROWS + 1).Copy
            Range("I" & MY_ROWS + 1).Resize(4, 5).PasteSpecial (xlPasteAll)
            Range("I" & MY_ROWS + 5).Formula = "=sum(I" & MY_ROWS + 1 & ":I" & MY_ROWS + 4 & ")"
            Range("I" & MY_ROWS + 5).Copy
            Range("J" & MY_ROWS + 5 & ":M" & MY_ROWS + 5).PasteSpecial (xlPasteAll)
        End If
    Next MY_ROWS
End Sub

not entirely sure if it correct - will look agaIN LATER.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

this seems to give the same results as your example

Code:
Sub Macro3()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & MY_ROWS).Value <> Range("A" & MY_ROWS + 1).Value Then
            Rows(MY_ROWS + 1).Resize(7).Insert
        End If
    Next MY_ROWS
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
        If IsEmpty(Range("A" & MY_ROWS).Value) And Not (IsEmpty(Range("A" & MY_ROWS - 1).Value)) Then
            Range("H" & MY_ROWS + 1).Value = "0111"
            Range("H" & MY_ROWS + 2).Value = "0121"
            Range("H" & MY_ROWS + 3).Value = "0131"
            Range("H" & MY_ROWS + 4).Value = "0200"
            Range("H" & MY_ROWS + 5).Value = "Total"
            If IsEmpty(Range("E" & MY_ROWS).Offset(-2, 0).Value) Then
                MY_END_ROW = MY_ROWS
            Else
                MY_END_ROW = Range("E" & MY_ROWS - 1).End(xlUp).Row
            End If
            If MY_END_ROW = 1 Then MY_END_ROW = 2
            Range("I" & MY_ROWS + 1).Formula = _
                "=SUMIF($E$" & MY_ROWS - 1 & ":$E$" & MY_END_ROW & ",$H" & MY_ROWS + 1 & ",I$" & MY_ROWS - 1 & ":I$" & MY_END_ROW & ")"
            Range("I" & MY_ROWS + 1).Copy
            Range("I" & MY_ROWS + 1).Resize(4, 5).PasteSpecial (xlPasteAll)
            Range("I" & MY_ROWS + 5).Formula = "=sum(I" & MY_ROWS + 1 & ":I" & MY_ROWS + 4 & ")"
            Range("I" & MY_ROWS + 5).Copy
            Range("J" & MY_ROWS + 5 & ":M" & MY_ROWS + 5).PasteSpecial (xlPasteAll)
        End If
    Next MY_ROWS
End Sub
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Hi onlyadrafter,

This works great. At first when I posted the code it did not run because in my VBE I have the Option Explicit turned on so it required MY_ROWS and MY_END_ROW declared as variables. I declared them both as Long but not sure if that is the correct declaration. It did work with long, but as my data grows could that cause a problem.

Also, what is the possibility of adding to column G just to the left of total and have it say Region - AA and so on?
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767

ADVERTISEMENT

Try....

Code:
Sub tst()
Dim a, b(1 To 4, 1 To 5) As Integer, aa(), x As Integer, ii As Integer, i As Integer, n As Single
With Application
aa = Array("0111", "0121", "0131", "0200"): n = 1
a = Range("A1:M" & Cells(Rows.Count, 1).End(xlUp).Row + 1)
For i = 2 To UBound(a, 1) - 1
    ii = .Match(a(i, 5), aa, 0)
    n = n + 1
For x = 9 To 13: b(ii, x - 8) = a(i, x): Next
    If (a(i, 1) <> a(i + 1, 1)) Then
    Cells(n, 1).Offset(1).Resize(7).EntireRow.Insert
        With Cells(n, 7).Offset(2)
        .Resize(5) = "Region - " & a(i, 1)
        .Offset(, 1).Resize(4, 1) = Application.Transpose(Split("'" & Join(aa, ",'"), ","))
        .Offset(, 2).Resize(4, 5) = b
        .Offset(4, 1) = "Total"
        .Offset(4, 2).Resize(1, 5).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
        n = Cells(n, 1).End(xlDown).Row - 1
        End With
    Erase b
    End If
Next
End With
End Sub

HTH
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Hi facethegod,

I received a run-time error '13' Type Mismatch on this line

Code:
ii = .Match(a(i, 5), aa, 0)
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows

ADVERTISEMENT

Hello,

like this?

Code:
Sub Macro3()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("A" & MY_ROWS).Value <> Range("A" & MY_ROWS + 1).Value Then
            Rows(MY_ROWS + 1).Resize(7).Insert
        End If
    Next MY_ROWS
    For MY_ROWS = 2 To Range("A" & Rows.Count).End(xlUp).Row + 1
        If IsEmpty(Range("A" & MY_ROWS).Value) And Not (IsEmpty(Range("A" & MY_ROWS - 1).Value)) Then
            Range("H" & MY_ROWS + 1).Value = "0111"
            Range("H" & MY_ROWS + 2).Value = "0121"
            Range("H" & MY_ROWS + 3).Value = "0131"
            Range("H" & MY_ROWS + 4).Value = "0200"
            Range("H" & MY_ROWS + 5).Value = "Total"
            Range("G" & MY_ROWS + 5).Value = "Region - " & Range("A" & MY_ROWS - 1).Value
            If IsEmpty(Range("E" & MY_ROWS).Offset(-2, 0).Value) Then
                MY_END_ROW = MY_ROWS
            Else
                MY_END_ROW = Range("E" & MY_ROWS - 1).End(xlUp).Row
            End If
            If MY_END_ROW = 1 Then MY_END_ROW = 2
            Range("I" & MY_ROWS + 1).Formula = _
                "=SUMIF($E$" & MY_ROWS - 1 & ":$E$" & MY_END_ROW & ",$H" & MY_ROWS + 1 & ",I$" & MY_ROWS - 1 & ":I$" & MY_END_ROW & ")"
            Range("I" & MY_ROWS + 1).Copy
            Range("I" & MY_ROWS + 1).Resize(4, 5).PasteSpecial (xlPasteAll)
            Range("I" & MY_ROWS + 5).Formula = "=sum(I" & MY_ROWS + 1 & ":I" & MY_ROWS + 4 & ")"
            Range("I" & MY_ROWS + 5).Copy
            Range("J" & MY_ROWS + 5 & ":M" & MY_ROWS + 5).PasteSpecial (xlPasteAll)
        End If
    Next MY_ROWS
End Sub
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,225
Office Version
  1. 2016
Platform
  1. Windows
Yes that works great. What about the variables? Do I need to declare them?
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Yes you should really, but I'm lazy. Long should be fine.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top