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
.