CODEWITHSUNDEEP
excelvbaloopssum
mohamed ali maarouf
mohamed ali maarouf

Reputation: 13

I want to do a loop within a loop to do a sum of columns vba

I did a code but doesn't seem to run properly. This line is not working: V(i, j) = Sheets(i).Range("C2:C66605") + Sheets(j).Range("C2:C66605")

And i don't want to show the result on a table, i just want to assign each sum to a variable V(1,2)/ V(1,3)/V(1,3)...

And the variable has to be a table, i don't know how to declare this.

Help please! ```

Sub sommeV40()
Dim i As Integer, j As Integer
Dim V As Variant

For i = 1 To 17
For j = i + 1 To 18
    V(i, j) = Sheets(i).Range("C2:C66605") + Sheets(j).Range("C2:C66605")
    Next j
Next i
End Sub

Upvotes: 1

Views: 97

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Combine the Sums of Columns From Different Worksheets

  • This will populate the array (Data) with the sums and return the values in a new workbook.
Sub SumV40()

    Const Col As String = "C"
    Const fRow As Long = 2
    Const WorksheetsCount As Long = 18

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim coll As Collection: Set coll = New Collection
    
    Dim ws As Worksheet
    Dim rg As Range
    Dim lRow As Long
    Dim n As Long
    
    For n = 1 To WorksheetsCount
        Set ws = wb.Worksheets(n)
        lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
        Set rg = ws.Range(ws.Cells(fRow, Col), ws.Cells(lRow, Col))
        coll.Add Application.Sum(rg)
    Next n
    
    Dim Size As Long: Size = WorksheetsCount - 1
    Dim Data() As Variant: ReDim Data(1 To Size, 1 To Size)
    
    Dim r As Long, c As Long
    
    For r = 1 To Size
        For c = r + 1 To WorksheetsCount
            Data(r, c - 1) = coll(r) + coll(c)
        Next c
    Next r
    Set coll = Nothing
    
    With Workbooks.Add.Worksheets(1).Range("A1").Resize(Size, Size)
        .Value = Data
        .Worksheet.Parent.Saved = True ' for easy closing while testing
    End With

End Sub

Upvotes: 1

Related Questions