Macro to add total between changes in values

F

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,338
Office Version
  1. 365
  2. 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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Hi facethegod,

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

Code: 
ii = .Match(a(i, 5), aa, 0)
 
Upvote 0
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
 
Upvote 0
Yes that works great. What about the variables? Do I need to declare them?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
235
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
847
CrashOD
C
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
183
Alex Blakenburg
A
V
  • Question
Issue with scraping with VBA
Replies
1
Views
359
John_w
J
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
241
esben
E

Forum statistics

Threads
1,207,392
Messages
6,078,219
Members
446,322
Latest member
pebuje

Share this page

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
Back
Top