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