Summing of columns into specified cell loop

[sllaksvb]

Hi all,

I am very new to VBA and can't seem to figure out how to write the code for this.

I am trying to sum Range("B4:B9") and want to display the answer in B95, and would like to repeat this for columns B to BI.

I tried using For loop but I think my logic is wrong and syntax.

My code is:

Sub summing ()
Dim x As Integer
Dim y As Range

For x = 2 To 61
y = Cells(Cells(4, x), Cells(9, x))
Cells(95, x) = Application.WorksheetFunction.Sum(Cells(y, x))
Next x
End Sub



Thank you for your help!!! I really appreciate it :)

[Paul_Hossler]

Try



Set y = Range(Cells(4, x), Cells(9, x))
Cells(95, x) = Application.WorksheetFunction.Sum(y)




Some comments

1. I like to use meaningful variable names
2. You need the keyword 'Set' to assign to objects
3. Cells(..) by itself will always refer to the active worksheet, and I don't like to just assume
4. Using With/With End and prefixing an object with a dot (e.g. .Cells(4, colCounter) 'links' it to the With (technically not 100% correct)
5. Since my 'SumRange' is a range object, just .Sum(SumRange) is enough


NOT tested with real data




Option Explicit

Sub summing()
Dim colCounter As Long
Dim SumRange As Range

With ActiveSheet
For colCounter = 2 To 61
Set SumRange = Range(.Cells(4, colCounter), .Cells(9, colCounter))
.Cells(95, colCounter).Value = Application.WorksheetFunction.Sum(SumRange)
Next colCounter
End With
End Sub

[YasserKhalil]

Hello
Try this code as you intend to do


Sub SummingTest()
Dim x As Integer
Dim y As Range


For x = 2 To 61
Set y = Range(Cells(4, x), Cells(9, x))
Cells(95, x) = Application.WorksheetFunction.Sum(y)
Next x
End Sub


But I think it will be better to do that without using loops in that way


Sub SummingWithoutLoops()
With Range("B95:BI95")
.Formula = "=SUM(B4:B9)"
.Value = .Value
End With
End Sub

[sllaksvb]

Thank you Paul and Yasser for your help! Both codes work perfectly!

I ended up not using the loops as I believe it would take longer for the macro to run!
Appreciate you guys taking the time to help me learn.

[YasserKhalil]

You're welcome. Glad we can offer some help for you
Regards