vba code for SUM without using worksheetfunction

[GeorgeF211]

Hi All,


Should be a simple one: Is there a way of summing a range in VBA without using worksheetfunction?


Unfortunately google isn't a lot of use for this kind of thing, as searching for "without" doesn't really work.


Cheers,


George

 

[kaushik03]

Hi George,


May I know what is reson you want to achieve this without using WorksheetFunction?


Assuming your data ranges from A1 to A11 as follows:


Header1

1

2

3

4

5

6

7

8

9

10


The below code will give you the sum of the above values at the last available cell(in this case, you will get the result at A12):

[pre]

Sub WithoutSum()

Dim MyVal As Long

Do Until ActiveCell = ""

MyVal = MyVal + ActiveCell.Value
ActiveCell.Range("A2").Select
Loop
ActiveCell.Value = MyVal
MyVal = 0

End Sub

[/pre]

Hope this helps.


Kaushik

 

[Luke M]

You can also use the

Evaluate method, shown in this sample:

[pre]Sub test()
MsgBox Evaluate("=SUM(A1:C1)")
End Sub

[/pre]

 

[GeorgeF211]

cheers both

 

[GeorgeF211]

Is there any chance I can have a little debugging help here?

Basically, what this does (or should do) is find blank rows and title them based on the values in columns A through to C. It should then sum all data between the two blanks it's found.

There is a three tier hierarchy, so what it then divides this sum by the tier it's on (3 for the "top", 1 for the "bottom").

The error in this is that the RowHolder(counter) is stuck on RowHolder(3), it doesn't step down to 2 or 1.

Any idea how I sort this out?

[pre]

For counter = 3 To 1 Step -1
RowHolder(counter) = MasterLastRow
For i = MasterLastRow To 1 Step -1
If MasterSheet.Cells(i, 4) = "" Then
MasterSheet.Range(MasterSheet.Cells(i + 1, 1), MasterSheet.Cells(i + 1, counter)).Copy
MasterSheet.Range(MasterSheet.Cells(i, 5 - counter), MasterSheet.Cells(i, 4)).PasteSpecial xlPasteValues
MasterSheet.Range(MasterSheet.Cells(i, 5 - counter), MasterSheet.Cells(i, 4)).HorizontalAlignment = xlCenter
MasterSheet.Range(MasterSheet.Cells(i, 5 - counter), MasterSheet.Cells(i, 4)).Font.Bold = True
Set RangeHolder = MasterSheet.Range(MasterSheet.Cells(i, 5), MasterSheet.Cells(RowHolder(counter), 5))
Cells(i, 5).Value = WorksheetFunction.Sum(RangeHolder) / (4 - counter)
RowHolder(counter) = i - 1
End If
Next
Next

[/pre]

Thanks.

 

[Hui]

George


Can you please start a new post for a new question

It makes searching a lot easier for us all

 

[GeorgeF211]

sorry, will do

 

[kaushik03]

Glad that your problem is solved.