VBA Excel 2007 sum dynamic range of cells loop

[PatrickM]

Hi all

apologies for raising a "stupid" question. I am sure this is easy to solve but I do not see a solution myself

I have to create a Sum on columns from column B to J, however I am unable to loop this accordingly. The Sum field is placed below the last active cell in a specific column.
The rows of each created sheet will differ every time that is why I need a dynamic Sum field

I have found a way to sum up single columns but I cannot get this working into a loop:

With Obj1.ActiveSheet
If IsEmpty(.Cells(.Rows.Count, 2)) Then
With .Cells(.Rows.Count, 2).End(xlUp)
.Offset(1, 0).Formula = "=Sum($B$1:" & .Address & ")"
End With
End If

I tried to add a for...next loop with for k = 2 to 10 but I need to have a dynamic value created for "=Sum($B, whereas B needs to be the current active column.

I can do this manually per column but this does not really look nice.

Can anyone help me?

Thanks Patrick

 

[delaneyjm]

I may be reading/understanding this incorrectly, but does this work for you?

Sub Test()
    Dim rng As Range
    Dim rng2 As Range
    
    Set rng = Range("B65536").End(xlUp)
    Set rng2 = Range("J65536").End(xlUp)
    
    With rng2.Offset(2, 0)
        .Value = Application.Sum(Range(rng, rng2))
    End With
End Sub

 

[PatrickM]

Hi

no this does not do the trick I am afraid.
This function creates a sum of all last cells in each column instead of summing all cells for each column

To explain further:
I have a header row in row 1
I also have a file name column in Column A

Column B to J are populated with numbers taken from csv files.
The actual sheet populated is created in a new workbook (Obj1)

Row number can vary every time but Column always stay from A to J.
All columns will have equal numbers of rows.

Lets say in sheet1 I have 45 rows in sheet2 I have 67 rows.
In sheet1 I need to created sum values for B2:B45 in cell B46, C2:C45 in cell C46, ..., J2:J45 in J46 and accordingly for sheet2 for rows 2 to 67 in row 68...

 

[PatrickM]

Can anyone help?

 

[PatrickM]

Solved: Re: VBA Excel 2007 sum dynamic range of cells loop

Solution is

For k = 2 To 10
With Obj1.ActiveSheet
If IsEmpty(.Cells(.Rows.Count, k)) Then
With Obj1.ActiveSheet.Cells(.Rows.Count, k).End(xlUp)
.Offset(1, 0).Formula = "=Sum(" & Obj1.ActiveSheet.Cells(.Rows.Count, k).Address & ":" & .Address & ")"
End With

End If
End With

Next