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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.