Sum Column in Array

[Troubled User]

I have a two dimensional array (10,10) and want to sum the third column
without creating a new array. I have tried a variety of different methods
but can't seem to get it. Any help is much appreciated.

 

[Dave Peterson]

You could let excel help by using =sum() and =index(). Or you could loop
through that 3rd column and add each element to a total.

Option Explicit
Sub testme()

Dim myArr(1 To 10, 1 To 10) As Double

Dim myTotal As Double
Dim rCtr As Double
Dim cCtr As Double
Dim myTestTotal As Double

'just some test data
For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
For cCtr = LBound(myArr, 2) To UBound(myArr, 2)
myArr(rCtr, cCtr) = rCtr / cCtr
Next cCtr
Next rCtr

'let excel help
With Application
myTotal = .Sum(.Index(myArr, , 3))
End With

'or loop
myTestTotal = 0
For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
myTestTotal = myTestTotal + myArr(rCtr, 3)
Next rCtr

MsgBox myTotal & vbLf & myTestTotal

End Sub

 

[stanleydgromjr]

Troubled User,

Try:

Adjust the range in the below code.



VBA Code:
--------------------




Option Explicit
Option Base 1
Sub SumArray()
Dim MyArray, a As Long, MyTotal As Double
MyTotal = 0
MyArray = *Range("A1:J10")*
For a = LBound(MyArray) To UBound(MyArray)
MyTotal = MyTotal + MyArray(a, *3*)
Next a
MsgBox "The sum of the third column is = " & Format(MyTotal, "#,##0.00")
End Sub


--------------------





Have a great day,
Stan

 

[Troubled User]

Dave,

This is what I was trying, but it is returning 0, when I know there are
values... If I

msgbox(myArr(3,3)) = 10

I can write the runnin total, just thought this was cleaner and then puzzling.

Thanks!





'let excel help
With Application
myTotal = .Sum(.Index(myArr, , 3))
End With

Troubled User,

Try:

Adjust the range in the below code.

VBA Code:

 

[Dave Peterson]

Try looping through the values in column 3 of your array and test to see if the
values are really numeric.

If you have text (like '3 or ="3") in a cell (say A1), then a formula like
=sum(A1:a9) will ignore the value in the cell--even if it looks like a number.

For rCtr = LBound(myArr, 1) To UBound(myArr, 1)
msgbox myarr(rctr,3) & vblf & application.isnumber(myArr(rCtr, 3))
Next rCtr

Application.isnumber is the =isnumber() worksheet formula. It won't be fooled
by the strings that look like numbers. (VBA's isnumeric() is fooled by those
strings that look like numbers.)

 

[Troubled User]

Dave, I was loading the array and displaying on a UserForm and formatting the
numbers on the form (#,###). I then reload the form and am trying to total.
When the array is first loaded the total works, (numeric before on the form)
when it reloads the array it changes the data type. Thanks for the heads up.


Any simple advice for converting the text value back to to numeric in VBA...
I know it is going to be a formated as "#,###". I could write something
ugly, but I thought you may have something simple. Thanks in advance.

 

[Dave Peterson]

If you're confident that the string is nice (that the user hasn't changed your
formatted number), then:

Option Explicit
Sub testme()

Dim myStr As String
Dim myNum As Double

myStr = "1,234.56"
myNum = CDbl(myStr)

MsgBox myNum

'you may want to double check to see if it's a number
'but what happens if it's not???

myStr = "abcd"
On Error Resume Next
myNum = CDbl(myStr)
If Err.Number <> 0 Then
Err.Clear
'it's not a number that can be seen by cdbl
myNum = 0 'or something else
End If
On Error GoTo 0

MsgBox myNum


End Sub

 

[Troubled User]

Dave,

I reset the form everytime it loads, so I am confident..... I wrapped the
load to the Array in Cdbl() and it worked like a charm... Thanks so much!

 

[Dave Peterson]

If you're displaying this formatted value in a textbox that the users can
change, be very careful <vbg>.