Beginner on VBA Code- hoping for some help.

anne2021 · Dec 14, 2021

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.

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)))

earthworm · Dec 15, 2021

Since you already have city on column "D" use as an advantage to apply sumif formula instead of doing the above horrible vba.

johnnyL · Dec 15, 2021

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

StephenCrump · Dec 15, 2021

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.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

2

3

4

5

6

7

CHECK:

8

5

19

18

4

17

7

17

12

9

5

12

20

28

38

37

103

9

16

15

17

8

5

6

14

18

4

9

19

46

31

96

10

8

2

6

20

16

3

18

7

16

2

1

3

39

41

6

86

11

18

5

19

11

14

1

9

8

3

12

19

42

25

34

101

12

7

14

15

19

9

13

14

2

17

2

10

17

36

33

29

98

13

4

18

9

10

1

14

9

6

13

14

18

31

29

45

105

14

8

18

6

17

18

16

12

8

17

15

32

53

49

134

15

18

16

2

18

19

14

10

7

2

16

7

36

56

25

117

16

20

6

1

12

11

19

14

16

4

14

9

46

24

27

97

17

4

3

9

8

6

13

2

6

12

17

18

11

16

27

20

63

18

4

9

17

7

6

5

3

7

3

13

7

14

30

18

13

61

19

12

5

16

13

7

14

10

19

2

11

18

33

34

43

110

20

21

29

37

43

32

38

16

49

33

43

25

17

32

22

46

54

14

36

49

48

37

19

31

18

36

54

23

20

17

42

28

19

32

19

23

34

23

47

31

24

95

108

99

96

106

96

105

75

108

66

100

117

25

26

CHECK:

27

28

29

30

31

Sheet1

Cell Formulas
Range	Formula
X8:X10,X14:X19	X8	=SUM(H8:J8)
W11:W19	W11	=SUM(E11:G11)
Y8:Y13,Y17:Y19	Y8	=SUM(K8:M8)
Z8:Z16	Z8	=SUM(N8:P8)
AA8:AA19	AA8	=SUM(W8:Z8)
H27:P27	H27	=SUM(H8:H10)
E28:G28,K28:P28	K28	=SUM(K11:K13)
N29:P29,E29:J29	N29	=SUM(N14:N16)
E30:M30	E30	=SUM(E17:E19)
E31:P31	E31	=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

anne2021 · Dec 17, 2021

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.

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA
1
2
3
4
5
6
7 CHECK:
8 5 19 18 4 17 7 17 12 9 5 12 20 28 38 37 103 28 38 37 103
9 16 15 17 8 5 6 14 14 18 18 4 9 19 46 31 96 19 46 31 96
10 8 2 6 20 16 3 18 7 16 2 1 3 39 41 6 86 39 41 6 86
11 18 5 19 11 14 1 9 8 8 3 12 19 42 25 34 101 42 25 34 101
12 7 14 15 19 9 13 14 2 17 2 10 17 36 33 29 98 36 33 29 98
13 4 18 9 10 10 1 14 9 6 13 14 18 31 29 45 105 31 29 45 105
14 8 18 6 17 18 18 16 12 8 17 17 15 32 53 49 134 32 53 49 134
15 18 16 2 18 19 19 14 10 7 2 16 7 36 56 25 117 36 56 25 117
16 20 20 6 1 12 11 19 14 16 4 14 9 46 24 27 97 46 24 27 97
17 4 3 9 8 6 13 2 6 12 17 18 11 16 27 20 63 16 27 20 63
18 4 9 17 7 6 5 3 7 3 13 7 14 30 18 13 61 30 18 13 61
19 12 5 16 13 7 14 14 10 19 2 11 18 33 34 43 110 33 34 43 110
20
21 29 37 43 32 38 16 49 33 43 25 17 32
22 46 54 14 36 49 48 37 19 31 18 36 54
23 20 17 42 28 19 32 19 23 34 23 47 31
24 95 108 99 96 106 96 105 75 108 66 100 117
25
26 CHECK:
27 32 38 16 49 33 43 25 17 32
28 29 37 43 37 19 31 18 36 54
29 46 54 14 36 49 48 23 47 31
30 20 17 42 28 19 32 19 23 34
31 95 108 99 96 106 96 105 75 108 66 100 117
Sheet1
Cell Formulas
Range Formula
X8:X10,X14:X19 X8 =SUM(H8:J8)
W11:W19 W11 =SUM(E11:G11)
Y8:Y13,Y17:Y19 Y8 =SUM(K8:M8)
Z8:Z16 Z8 =SUM(N8:P8)
AA8:AA19 AA8 =SUM(W8:Z8)
H27:P27 H27 =SUM(H8:H10)
E28:G28,K28:P28 K28 =SUM(K11:K13)
N29:P29,E29:J29 N29 =SUM(N14:N16)
E30:M30 E30 =SUM(E17:E19)
E31:P31 E31 =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

Thank you very much! This code works perfectly.

anne2021 · Dec 17, 2021

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

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!

StephenCrump · Dec 17, 2021

anne2021 said:
Thank you very much! This code works perfectly.

I'm glad we could help. Thanks for posting back.

johnnyL · Dec 17, 2021

@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.

anne2021 · Dec 18, 2021

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.

Thanks for letting me know. Sorry I thought I had marked his message as solution. I had marked his message instead.

Beginner on VBA Code- hoping for some help.

anne2021

New Member

Excel Facts

earthworm

Well-known Member

johnnyL

Well-known Member

StephenCrump

MrExcel MVP

anne2021

New Member

anne2021

New Member

StephenCrump

MrExcel MVP

johnnyL

Well-known Member

anne2021

New Member

Similar threads

Forum statistics

Share this page

Beginner on VBA Code- hoping for some help.

New Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

New Member

New Member

MrExcel MVP

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock