PDA

View Full Version : Solved: Calculation with an asterisk



av8tordude
06-23-2011, 07:46 AM
How can I calculate a column that have numbers with or without an asterisk. The code below works fine, but it does not include numbers with the asterisk

.Sum(Range("I11:I375")).
example:

$38.25
$51.00*
$71.12
$61.24*
$38.25
$51.89
$71.00*
$61.24*

Kenneth Hobs
06-23-2011, 08:06 AM
How can you sum a number if it is a string? Use numeric format to show the string with an asterisk but keep it as a number. Then, your math functions will work properly. Otherwise, you have to do so other hoops to achieve your goal.

av8tordude
06-23-2011, 09:22 AM
Use numeric format to show the string with an asterisk but keep it as a number.

What do you mean and how to do I go about accomplish it?

Bob Phillips
06-23-2011, 09:37 AM
Try this ARRAY formula

=SUM(IF(H1:H10<>"",--SUBSTITUTE(SUBSTITUTE(H1:H10,"$",""),"*","")))

av8tordude
06-23-2011, 09:40 AM
Ok..here what I did to keep my existing code...which works

I changed the cell format to $#,##0.00"*". Therefore any number entered will be displayed with an asterisk.

Now how do I make this statement true?

If active cell has "*" then
frmCalc.ckPR = True
End If

Bob Phillips
06-23-2011, 09:50 AM
frmCalc.ckPR = Instr(Activecell.Value, "*") > 0

Kenneth Hobs
06-23-2011, 10:09 AM
Here is one way to convert the data with an example set for illustration in the first part.
Sub t()
Dim r As Range, c As Range, d As Double
'.NumberFormat = "$#,##0.00"
Range("I11").NumberFormat = "General"
Range("I11").Value = 1
Range("I12").NumberFormat = "Text"
Range("I12").Value = "$1,000.00*"
Range("I13").NumberFormat = "General"
Range("I13").Value = 1
MsgBox Evaluate("Sum(I11:I375)")

Set r = Range("I11:I1375")
For Each c In r
If VarType(c) = 8 Then _
c.Value2 = Val(Replace(Replace(Replace(c.Value2, "*", ""), "$", ""), ",", ""))
Next c
r.NumberFormat = "$#,##0.00"

MsgBox Format(Evaluate("Sum(I11:I375)"), "$#,##0.00")
End Sub

av8tordude
06-23-2011, 10:19 AM
frmCalc.ckPR = Instr(Activecell.Value, "*") > 0

XLD,

The checkbox is not getting checked if the number has an asterisk

av8tordude
06-23-2011, 10:24 AM
I discovered why its not working...

The cell is formatted as activecell.NumberFormat = "$###0.00""*""". Therefore the number is entered as a number, but displayed with an asterisk.

So how do I make this statement true if it is entered as a number , bu displayed with an asterisk?

If active cell has "*" Then frmCalc.ckPR = True

Bob Phillips
06-23-2011, 10:33 AM
Use Text not Value.

av8tordude
06-23-2011, 10:38 AM
Ken & XLD,

Thank you for your tremendous help. I really appreciated it.:friends: