Solved: Check if number has a asterisk

[av8tordude]

I would like test if the number in a cell has an asterisk at the end of the number.

i.e.

If $42.38* has an asterisk then msgbox "Yes"

if $67.36 has a astersik then msgbox "No"

[Trebor76]

Hi av8tordude,

Though an entry with an asterisk at the end isn't a number anyway, try this which checks the entry in A1 (change to suit):


Sub Macro1()
If Right([A1], 1) = "*" Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub

HTH

Robert

[av8tordude]

Actually, the cell is formatted to put an asterisk at the end of the number. Will this work since its a formatted number

[Trebor76]

Not sure - give it a try.

[mikerickson]

If the cell is formatted to add an asterisk after a number then IsNumeric should work.
Or you could test

If Range("A1").Text Like "* " Then

[av8tordude]

Thank you both for the code...

One question, If I want to test to see if the text in the cell is formatted like $0.00, what would the code be?

I've tried replacing "*
" with $ but it doesn't work

Thanks

[Trebor76]

To see the number format of the active cell (i.e. the cell you're on when you run the code) use this:


Sub Macro8()

MsgBox ActiveCell.NumberFormat

End Sub

[av8tordude]

Hi trebor,

Pardon my confusion, but let me re-explain....

using the same principal in my original question, I want to test if the text in the cell does NOT have an asterisk.

[Aussiebear]

Simply reverse the test as outlined in post#2

[av8tordude]

Its not that simple.

This code I using is to test if there is an asterisk, if so, then do option 1 ...

If ActiveSheet.Cells(Cur_Row - 1, 10).Text Like "*
" Then

but now i need to test if the text does not have an asterisk.

[Trebor76]

but now i need to test if the text does not have an asterisk.

So this would be the 'Else' part of your code, i.e.


Sub Macro1()
'If there's an asterisk in the 'Cur_Row - 1, Row of Column 'J', then...
If ActiveSheet.Cells(Cur_Row - 1, 10).Text Like "*
" Then
'...return a message box simply saying 'Yes'.
MsgBox "Yes"
'Else...
Else
'...return a message box simply saying 'No'.
MsgBox "Yes"
End If
End Sub

[av8tordude]

I know how to do an if/else statement. what I would like to know is...what is the opposite of this code?

If ActiveCell.Text Like "*
" Then -> this checks to see if the text has an asterisk. What is the opposite of this code to check if it does not have an asterisk. I used yes/no as an example, because I was planning to use the if statement with a different code.

e.g. if 63.54 has no asterisk Then....

[frank_m]

If Not ActiveSheet.Cells(Cur_Row - 1, 10).Text Like "*
" Then
'.......

[av8tordude]

Thank you frank. This is what I was looking for. :friends:

[mikerickson]

The "reverse" of
If someCondition Then
Rem do something
Else
Rem do other thing
End If
is either
If someCondition Then
Rem do other thing
Else
Rem do something
End If
or
If Not(someCondition) Then
Rem do something
Else
Rem do other thing
End If