Beginner on VBA Code- hoping for some help.

A

anne2021

New Member
Joined
Dec 4, 2021
Messages
9
Office Version
  1. 2021
Platform
  1. MacOS
Hi everyone, I hope someone can help in constructing VBA scripts for my data:

I have this matrix table (see pic attached). I used the following codes to generate the sums of columns and rows vector but not including the diagonal data (shaded in green). I'm a beginner with VBA. I used to construct this code but it's long. I know we can use loop to define and generate scripts so I can get my calculations.

For example, getting the sum of data in ranges E11:E13 and returning the value in E21 which is 81.90.. and the same process goes for the rest..
For getting the sum of H8:J8 and returning the value in R8 which is 28.

Hope someone can help...

Many thanks in advance.
Picture1-3.png


Sub Test_ROW_ColumnTitles()

countrylist = ThisWorkbook.Worksheets("Sheet3").Range("C4:C7")
industrylist = ThisWorkbook.Worksheets("Sheet3").Range("B4:B6")

For i = 1 To 4
For j = 1 To 3
ThisWorkbook.Worksheets("Sheet3").Cells((i - 1) * 3 + j + 7, 4) = _
countrylist(i, 1) & "_" & industrylist(j, 1)
ThisWorkbook.Worksheets("Sheet3").Cells(7, (i - 1) * 3 + j + 4) = _
countrylist(i, 1) & "_" & industrylist(j, 1)
Next j
Next i

End Sub

Public Sub Copy()

Worksheets("Sheet1").Range("E6:P17").Copy _
Destination:=Worksheets("Sheet3").Range("E8")


End Sub

Public Sub Sum()

' Row, Column
' Calculation Below

Range("E21").Value = Application.Sum(Range(Cells(11, 5), Cells(13, 5)))
Range("E22").Value = Application.Sum(Range(Cells(14, 5), Cells(16, 5)))
Range("E23").Value = Application.Sum(Range(Cells(17, 5), Cells(19, 5)))
Range("E24").Value = Application.Sum(Range(Cells(11, 5), Cells(19, 5)))

Range("F21").Value = Application.Sum(Range(Cells(11, 6), Cells(13, 6)))
Range("F22").Value = Application.Sum(Range(Cells(14, 6), Cells(16, 6)))
Range("F23").Value = Application.Sum(Range(Cells(17, 6), Cells(19, 6)))
Range("F24").Value = Application.Sum(Range(Cells(11, 6), Cells(19, 6)))

Range("G21").Value = Application.Sum(Range(Cells(11, 7), Cells(13, 7)))
Range("G22").Value = Application.Sum(Range(Cells(14, 7), Cells(16, 7)))
Range("G23").Value = Application.Sum(Range(Cells(17, 7), Cells(19, 7)))
Range("G24").Value = Application.Sum(Range(Cells(11, 7), Cells(19, 7)))


Range("H21").Value = Application.Sum(Range(Cells(8, 8), Cells(10, 8)))
Range("H22").Value = Application.Sum(Range(Cells(14, 8), Cells(16, 8)))
Range("H23").Value = Application.Sum(Range(Cells(17, 8), Cells(19, 8)))
Range("H24").Value = Application.Sum(Range(Cells(8, 8), Cells(10, 8))) + Application.Sum(Range(Cells(14, 8), Cells(19, 8)))

Range("I21").Value = Application.Sum(Range(Cells(8, 9), Cells(10, 9)))
Range("I22").Value = Application.Sum(Range(Cells(14, 9), Cells(16, 9)))
Range("I23").Value = Application.Sum(Range(Cells(17, 9), Cells(19, 9)))
Range("I24").Value = Application.Sum(Range(Cells(8, 9), Cells(10, 9))) + Application.Sum(Range(Cells(14, 9), Cells(19, 9)))

Range("J21").Value = Application.Sum(Range(Cells(8, 10), Cells(10, 10)))
Range("J22").Value = Application.Sum(Range(Cells(14, 10), Cells(16, 10)))
Range("J23").Value = Application.Sum(Range(Cells(17, 10), Cells(19, 10)))
Range("J24").Value = Application.Sum(Range(Cells(8, 10), Cells(10, 10))) + Application.Sum(Range(Cells(14, 10), Cells(19, 10)))

Range("K21").Value = Application.Sum(Range(Cells(8, 11), Cells(10, 11)))
Range("K22").Value = Application.Sum(Range(Cells(11, 11), Cells(13, 11)))
Range("K23").Value = Application.Sum(Range(Cells(17, 11), Cells(19, 11)))
Range("K24").Value = Application.Sum(Range(Cells(8, 11), Cells(13, 11))) + Application.Sum(Range(Cells(17, 11), Cells(19, 11)))

Range("L21").Value = Application.Sum(Range(Cells(8, 12), Cells(10, 12)))
Range("L22").Value = Application.Sum(Range(Cells(11, 12), Cells(13, 12)))
Range("L23").Value = Application.Sum(Range(Cells(17, 12), Cells(19, 12)))
Range("L24").Value = Application.Sum(Range(Cells(8, 12), Cells(13, 12))) + Application.Sum(Range(Cells(17, 12), Cells(19, 12)))

Range("M21").Value = Application.Sum(Range(Cells(8, 13), Cells(10, 13)))
Range("M22").Value = Application.Sum(Range(Cells(11, 13), Cells(13, 13)))
Range("M23").Value = Application.Sum(Range(Cells(17, 13), Cells(19, 13)))
Range("M24").Value = Application.Sum(Range(Cells(8, 13), Cells(13, 13))) + Application.Sum(Range(Cells(17, 13), Cells(19, 13)))


Range("N21").Value = Application.Sum(Range(Cells(8, 14), Cells(10, 14)))
Range("N22").Value = Application.Sum(Range(Cells(11, 14), Cells(13, 14)))
Range("N23").Value = Application.Sum(Range(Cells(14, 14), Cells(16, 14)))
Range("N24").Value = Application.Sum(Range(Cells(8, 14), Cells(16, 14)))

Range("O21").Value = Application.Sum(Range(Cells(8, 15), Cells(10, 15)))
Range("O22").Value = Application.Sum(Range(Cells(11, 15), Cells(13, 15)))
Range("O23").Value = Application.Sum(Range(Cells(14, 15), Cells(16, 15)))
Range("O24").Value = Application.Sum(Range(Cells(8, 15), Cells(16, 15)))

Range("P21").Value = Application.Sum(Range(Cells(8, 16), Cells(10, 16)))
Range("P22").Value = Application.Sum(Range(Cells(11, 16), Cells(13, 16)))
Range("P23").Value = Application.Sum(Range(Cells(14, 16), Cells(16, 16)))
Range("P24").Value = Application.Sum(Range(Cells(8, 16), Cells(16, 16)))

' Row, Column
' Calculation Right

Range("R8").Value = Application.Sum(Range(Cells(8, 8), Cells(8, 10)))
Range("S8").Value = Application.Sum(Range(Cells(8, 11), Cells(8, 13)))
Range("T8").Value = Application.Sum(Range(Cells(8, 14), Cells(8, 16)))
Range("U8").Value = Application.Sum(Range(Cells(8, 8), Cells(8, 16)))

Range("R9").Value = Application.Sum(Range(Cells(9, 8), Cells(9, 10)))
Range("S9").Value = Application.Sum(Range(Cells(9, 11), Cells(9, 13)))
Range("T9").Value = Application.Sum(Range(Cells(9, 14), Cells(9, 16)))
Range("U9").Value = Application.Sum(Range(Cells(9, 8), Cells(9, 16)))

Range("R10").Value = Application.Sum(Range(Cells(10, 8), Cells(10, 10)))
Range("S10").Value = Application.Sum(Range(Cells(10, 11), Cells(10, 13)))
Range("T10").Value = Application.Sum(Range(Cells(10, 14), Cells(10, 16)))
Range("U10").Value = Application.Sum(Range(Cells(10, 8), Cells(10, 16)))


Range("R11").Value = Application.Sum(Range(Cells(11, 5), Cells(11, 7)))
Range("S11").Value = Application.Sum(Range(Cells(11, 11), Cells(11, 13)))
Range("T11").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
Range("U11").Value = Application.Sum(Range(Cells(11, 5), Cells(11, 7))) + Application.Sum(Range(Cells(14, 11), Cells(19, 16)))

Range("R12").Value = Application.Sum(Range(Cells(12, 5), Cells(12, 7)))
Range("S12").Value = Application.Sum(Range(Cells(12, 11), Cells(12, 13)))
Range("T12").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
Range("U12").Value = Application.Sum(Range(Cells(12, 5), Cells(12, 7))) + Application.Sum(Range(Cells(14, 11), Cells(19, 16)))

Range("R13").Value = Application.Sum(Range(Cells(13, 5), Cells(13, 7)))
Range("S13").Value = Application.Sum(Range(Cells(13, 11), Cells(13, 13)))
Range("T13").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
Range("U13").Value = Application.Sum(Range(Cells(13, 5), Cells(13, 7))) + Application.Sum(Range(Cells(14, 11), Cells(19, 16)))


Range("R14").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
Range("S14").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
Range("T14").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
Range("U14").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 10))) + Application.Sum(Range(Cells(17, 14), Cells(19, 11)))

Range("R15").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
Range("S15").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
Range("T15").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
Range("U15").Value = Application.Sum(Range(Cells(15, 5), Cells(15, 10))) + Application.Sum(Range(Cells(17, 14), Cells(19, 12)))

Range("R16").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
Range("S16").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
Range("T16").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
Range("U16").Value = Application.Sum(Range(Cells(16, 5), Cells(16, 10))) + Application.Sum(Range(Cells(17, 14), Cells(19, 13)))


Range("R17").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
Range("S17").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
Range("T17").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
Range("U17").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 13)))

Range("R18").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
Range("S18").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
Range("T18").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
Range("U18").Value = Application.Sum(Range(Cells(18, 5), Cells(18, 13)))
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Since you already have city on column "D" use as an advantage to apply sumif formula instead of doing the above horrible vba.
 
Upvote 0
This is the shorter version I have come up with thus far:

VBA Code: 
Sub Test_ROW_ColumnTitles()
'
    countrylist = ThisWorkbook.Worksheets("Sheet3").Range("C4:C7")
    industrylist = ThisWorkbook.Worksheets("Sheet3").Range("B4:B6")
'
    For i = 1 To 4
        For j = 1 To 3
            ThisWorkbook.Worksheets("Sheet3").Cells((i - 1) * 3 + j + 7, 4) = countrylist(i, 1) & "_" & industrylist(j, 1)
            ThisWorkbook.Worksheets("Sheet3").Cells(7, (i - 1) * 3 + j + 4) = countrylist(i, 1) & "_" & industrylist(j, 1)
        Next
    Next
End Sub

Public Sub Copy()
'
    Worksheets("Sheet1").Range("E6:P17").Copy Destination:=Worksheets("Sheet3").Range("E8")
End Sub

Public Sub Sum()
'
    Range("E21").Resize(1, 3).Formula = "=Sum(E11 + E13)"
    Range("H21").Resize(1, 9).Formula = "=Sum(H8 + H10)"
    Range("E22").Resize(1, 6).Formula = "=Sum(E14 + E16)"
    Range("K22").Resize(1, 6).Formula = "=Sum(K11 + K13)"
    Range("E23").Resize(1, 9).Formula = "=Sum(E17 + E19)"
    Range("N23").Resize(1, 3).Formula = "=Sum(N14 + N16)"
    Range("E24").Resize(1, 3).Formula = "=Sum(E11 + E19)"
    Range("H24").Resize(1, 3).Formula = "=Sum(H8 + H10 + H14 + H19)"
    Range("K24").Resize(1, 3).Formula = "=Sum(K8 + K13 + K17 + K19)"
    Range("N24").Resize(1, 3).Formula = "=Sum(N8 + N16)"
'
    Range("R8").Resize(1, 3).Formula = "=Sum(H8 + J8)"
    Range("R11").Resize(1, 3).Formula = "=Sum(E11 + G11)"
'
    Range("R14").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R15").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R16").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R17").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
    Range("R18").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
'
    Range("S8").Resize(1, 6).Formula = "=Sum(K8 + M8)"
'
    Range("S14").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S15").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S16").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S17").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
    Range("S18").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
'
    Range("T8").Resize(1, 3).Formula = "=Sum(N8 + P8)"
'
    Range("T11").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T12").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T13").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T14").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T15").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T16").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T17").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
    Range("T18").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
'
    Range("U8").Resize(1, 3).Formula = "=Sum(H8 + P8)"
'
    Range("U11").Resize(1, 3).Formula = "=Sum(E11 + G11 + K14 + P19)"
'
    Range("U14").Resize(1, 3).Formula = "=Sum(E14 + J14 + N17 + K19)"
'
    Range("U17").Resize(1, 2).Formula = "=Sum(E17 + M17)"
End Sub
 
Upvote 0
With all those hard-coded cell references, this will be a nightmare to update if you start rearranging the worksheet!

Here's a more robust way you could do it, using formulae. It's also tempting to take the data array entirely into VBA and loop to calculate the various totals.

My personal preference would be to leave the Excel formulae in place, rather than just the values.

ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3
4
5
6
7CHECK:
85191841771712951220283837103283837103
916151785614141818491946319619463196
10826201631871621339416863941686
11185191114198831219422534101422534101
12714151991314217210173633299836332998
134189101011496131418312945105312945105
14818617181816128171715325349134325349134
1518162181919141072167365625117365625117
16202061121119141641494624279746242797
17439861326121718111627206316272063
184917765373137143018136130181361
19125161371414101921118333443110333443110
20
21293743323816493343251732
22465414364948371931183654
23201742281932192334234731
24951089996106961057510866100117
25
26CHECK:
27323816493343251732
28293743371931183654
29465414364948234731
30201742281932192334
31951089996106961057510866100117
Sheet1
Cell Formulas
RangeFormula
X8:X10,X14:X19X8=SUM(H8:J8)
W11:W19W11=SUM(E11:G11)
Y8:Y13,Y17:Y19Y8=SUM(K8:M8)
Z8:Z16Z8=SUM(N8:P8)
AA8:AA19AA8=SUM(W8:Z8)
H27:P27H27=SUM(H8:H10)
E28:G28,K28:P28K28=SUM(K11:K13)
N29:P29,E29:J29N29=SUM(N14:N16)
E30:M30E30=SUM(E17:E19)
E31:P31E31=SUM(E27:E30)


MyData: =Sheet1!$E$8:$P$19

VBA Code: 
Sub GetTotals()

    Const N = 4
   
    With Range("MyData")
        With .Offset(.Rows.count + 1).Resize(N)
            .Formula2 = "=LET(rw,3,c,3,rws,ROWS(" & .Cells(1).Address(, 0) & ":" & .Cells(1).Address(0, 0) & "),cols,COLUMNS(" & .Cells(1).Address(0) & ":" & .Cells(1).Address(0, 0) & "),SUM(INDEX(MyData,rw*(rws-1+(rws>INT((cols-1)/c)))+SEQUENCE(rw),cols)))"
            .Rows(N).FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
            .Value = .Value 'optional - you can leave the formulae if you like
        End With
        With .Offset(, .Columns.count + 1).Resize(, N)
            .Formula2 = "=LET(rw,3,c,3,rws,ROWS(" & .Cells(1).Address(, 0) & ":" & .Cells(1).Address(0, 0) & "),cols,COLUMNS(" & .Cells(1).Address(0) & ":" & .Cells(1).Address(0, 0) & "),SUM(INDEX(MyData,rws,c*(cols-1+(cols>INT((rws-1)/rw)))+SEQUENCE(,c))))"
            .Columns(N).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
            .Value = .Value 'optional - you can leave the formulae if you like
        End With
    End With

End Sub
 
Upvote 0
Solution
StephenCrump said:
With all those hard-coded cell references, this will be a nightmare to update if you start rearranging the worksheet!

Here's a more robust way you could do it, using formulae. It's also tempting to take the data array entirely into VBA and loop to calculate the various totals.

My personal preference would be to leave the Excel formulae in place, rather than just the values.

ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2
3
4
5
6
7CHECK:
85191841771712951220283837103283837103
916151785614141818491946319619463196
10826201631871621339416863941686
11185191114198831219422534101422534101
12714151991314217210173633299836332998
134189101011496131418312945105312945105
14818617181816128171715325349134325349134
1518162181919141072167365625117365625117
16202061121119141641494624279746242797
17439861326121718111627206316272063
184917765373137143018136130181361
19125161371414101921118333443110333443110
20
21293743323816493343251732
22465414364948371931183654
23201742281932192334234731
24951089996106961057510866100117
25
26CHECK:
27323816493343251732
28293743371931183654
29465414364948234731
30201742281932192334
31951089996106961057510866100117
Sheet1
Cell Formulas
RangeFormula
X8:X10,X14:X19X8=SUM(H8:J8)
W11:W19W11=SUM(E11:G11)
Y8:Y13,Y17:Y19Y8=SUM(K8:M8)
Z8:Z16Z8=SUM(N8:P8)
AA8:AA19AA8=SUM(W8:Z8)
H27:P27H27=SUM(H8:H10)
E28:G28,K28:P28K28=SUM(K11:K13)
N29:P29,E29:J29N29=SUM(N14:N16)
E30:M30E30=SUM(E17:E19)
E31:P31E31=SUM(E27:E30)


MyData: =Sheet1!$E$8:$P$19

VBA Code: 
Sub GetTotals()

    Const N = 4
  
    With Range("MyData")
        With .Offset(.Rows.count + 1).Resize(N)
            .Formula2 = "=LET(rw,3,c,3,rws,ROWS(" & .Cells(1).Address(, 0) & ":" & .Cells(1).Address(0, 0) & "),cols,COLUMNS(" & .Cells(1).Address(0) & ":" & .Cells(1).Address(0, 0) & "),SUM(INDEX(MyData,rw*(rws-1+(rws>INT((cols-1)/c)))+SEQUENCE(rw),cols)))"
            .Rows(N).FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
            .Value = .Value 'optional - you can leave the formulae if you like
        End With
        With .Offset(, .Columns.count + 1).Resize(, N)
            .Formula2 = "=LET(rw,3,c,3,rws,ROWS(" & .Cells(1).Address(, 0) & ":" & .Cells(1).Address(0, 0) & "),cols,COLUMNS(" & .Cells(1).Address(0) & ":" & .Cells(1).Address(0, 0) & "),SUM(INDEX(MyData,rws,c*(cols-1+(cols>INT((rws-1)/rw)))+SEQUENCE(,c))))"
            .Columns(N).FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
            .Value = .Value 'optional - you can leave the formulae if you like
        End With
    End With

End Sub
Click to expand...


Thank you very much! This code works perfectly.
 
Upvote 0
johnnyL said:
This is the shorter version I have come up with thus far:

VBA Code: 
Sub Test_ROW_ColumnTitles()
'
    countrylist = ThisWorkbook.Worksheets("Sheet3").Range("C4:C7")
    industrylist = ThisWorkbook.Worksheets("Sheet3").Range("B4:B6")
'
    For i = 1 To 4
        For j = 1 To 3
            ThisWorkbook.Worksheets("Sheet3").Cells((i - 1) * 3 + j + 7, 4) = countrylist(i, 1) & "_" & industrylist(j, 1)
            ThisWorkbook.Worksheets("Sheet3").Cells(7, (i - 1) * 3 + j + 4) = countrylist(i, 1) & "_" & industrylist(j, 1)
        Next
    Next
End Sub

Public Sub Copy()
'
    Worksheets("Sheet1").Range("E6:P17").Copy Destination:=Worksheets("Sheet3").Range("E8")
End Sub

Public Sub Sum()
'
    Range("E21").Resize(1, 3).Formula = "=Sum(E11 + E13)"
    Range("H21").Resize(1, 9).Formula = "=Sum(H8 + H10)"
    Range("E22").Resize(1, 6).Formula = "=Sum(E14 + E16)"
    Range("K22").Resize(1, 6).Formula = "=Sum(K11 + K13)"
    Range("E23").Resize(1, 9).Formula = "=Sum(E17 + E19)"
    Range("N23").Resize(1, 3).Formula = "=Sum(N14 + N16)"
    Range("E24").Resize(1, 3).Formula = "=Sum(E11 + E19)"
    Range("H24").Resize(1, 3).Formula = "=Sum(H8 + H10 + H14 + H19)"
    Range("K24").Resize(1, 3).Formula = "=Sum(K8 + K13 + K17 + K19)"
    Range("N24").Resize(1, 3).Formula = "=Sum(N8 + N16)"
'
    Range("R8").Resize(1, 3).Formula = "=Sum(H8 + J8)"
    Range("R11").Resize(1, 3).Formula = "=Sum(E11 + G11)"
'
    Range("R14").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R15").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R16").Value = Application.Sum(Range(Cells(14, 5), Cells(14, 7)))
    Range("R17").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
    Range("R18").Value = Application.Sum(Range(Cells(17, 5), Cells(17, 7)))
'
    Range("S8").Resize(1, 6).Formula = "=Sum(K8 + M8)"
'
    Range("S14").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S15").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S16").Value = Application.Sum(Range(Cells(14, 8), Cells(14, 10)))
    Range("S17").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
    Range("S18").Value = Application.Sum(Range(Cells(17, 8), Cells(17, 10)))
'
    Range("T8").Resize(1, 3).Formula = "=Sum(N8 + P8)"
'
    Range("T11").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T12").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T13").Value = Application.Sum(Range(Cells(14, 11), Cells(19, 16)))
    Range("T14").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T15").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T16").Value = Application.Sum(Range(Cells(17, 14), Cells(19, 11)))
    Range("T17").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
    Range("T18").Value = Application.Sum(Range(Cells(17, 11), Cells(17, 13)))
'
    Range("U8").Resize(1, 3).Formula = "=Sum(H8 + P8)"
'
    Range("U11").Resize(1, 3).Formula = "=Sum(E11 + G11 + K14 + P19)"
'
    Range("U14").Resize(1, 3).Formula = "=Sum(E14 + J14 + N17 + K19)"
'
    Range("U17").Resize(1, 2).Formula = "=Sum(E17 + M17)"
End Sub
Click to expand...
Thank you so much for the time and help. The code to sum the vertical data works, but summing up the horizontal data did not get the correct figures. Appreciated the help very much!
 
Upvote 0
@anne2021 Thank you for responding back. You have check marked your post as the solution. You should check mark the post that produced the solution, the post by @StephenCrump , not your post. It is easier to follow the thread that way. ;)
 
Upvote 0
johnnyL said:
@anne2021 Thank you for responding back. You have check marked your post as the solution. You should check mark the post that produced the solution, the post by @StephenCrump , not your post. It is easier to follow the thread that way. ;)
Click to expand...
Thanks for letting me know. Sorry I thought I had marked his message as solution. I had marked his message instead.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
TEXTSPLIT into Rows but Maintain Grouping
Replies
5
Views
112
Cubist
Cubist
G
  • Solved
Formula or macro to retrieve cell above to replace zero
Replies
3
Views
96
Peter_SSs
Peter_SSs
J
  • Question Question
Warning message that file is read only
Replies
6
Views
157
RoryA
RoryA
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
O
  • Solved
Conditional Format Compare Ranges
Replies
3
Views
35
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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