Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to manipulate an 2D Array into a Column Array? | Excel Discussion (Misc queries) | |||
Finding a number in a column out of an array in another column | Excel Discussion (Misc queries) | |||
How do I copy a data from a single column into an array and back into another column? | Excel Programming | |||
Put 1D array into column | Excel Programming | |||
calculting stats of column array in 3D array | Excel Programming |