View Full Version : [SOLVED:] Summing of columns into specified cell loop
sllaksvb
08-03-2017, 12:57 PM
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
08-03-2017, 02:29 PM
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
08-03-2017, 02:40 PM
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
08-04-2017, 07:31 AM
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
08-04-2017, 08:08 AM
You're welcome. Glad we can offer some help for you
Regards
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.