Sum Column in Array
 

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.

Sum Column in Array
 

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

Troubled User wrote:

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

Sum Column in Array
 

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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

Sum Column in Array
 

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



"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.

Sum Column in Array
 

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 wrote:

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

"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.


--

Dave Peterson

Sum Column in Array
 

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" wrote:

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 wrote:

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

"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.


--

Dave Peterson
.

Sum Column in Array
 

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 wrote:

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" wrote:

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 wrote:

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

"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.


--

Dave Peterson
.


--

Dave Peterson

Sum Column in Array
 

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.


--

Dave Peterson
.


--

Dave Peterson
.

Sum Column in Array
 

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



Troubled User wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

"stanleydgromjr" wrote:


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


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193109

http://www.thecodecage.com/forumz

.


--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson