Summing up numbers from a single textbox on a userform

[Lori_H]

Hello all,

I've been trying to figure this one out on my own, but can't seem to. So I turn to you all!

Here's what I'd like to do:

On a userform, I'd have two textboxes: One for a person's name, and one for the amounts of refunds they may have processed. i.e., Joe has turned in handwritten worksheet to me that says he processed three refunds for 2.00, 4.99 and 3.50.

Sally will type "Joe" into the name textbox. She'll then type "2 4.99 3.50" or "2.00, 4.99, 3.50" into the second textbox.

I'd like to take the numbers she enters into the second textbox and add them together with the result being placed in a cell on a worksheet.

What I haven't been able to figure out is how to separate out the list of numbers (either space or comma delimited) and sum them up.

Suggestions??

Much appreciation in advance!

 

[Domenic]

Maybe something like this...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] sText [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] vRefunds [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vAmount [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] MySum [color=darkblue]As[/color] [color=darkblue]Double[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    sText = Me.TextBox2.Value
    sText = Replace(sText, ",", " ")
    sText = Application.Trim(sText)
    vRefunds = Split(sText, " ")
    MySum = 0
    [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vRefunds)
        vAmount = vRefunds(i)
        [color=darkblue]If[/color] IsNumeric(vAmount) [color=darkblue]Then[/color]
            MySum = MySum + [color=darkblue]CDbl[/color](vAmount)
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    MsgBox MySum
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Note that the code needs to be placed in the code module for the UserForm. Change the name of the CommandButton and TextBox accordingly.

Hope this helps!

 

[Norie]

You could try this but note it has absolutely no error checking or validation.

varSum = Evaluate(Replace(TextBox1.Value, " ", "+")

Range("A1").Value = varSum

 

[Lori_H]

Thank you so much! Stepping through it has helped me learn a lot!