#1
IsNumeric(Cells(1,1))
#2
What do you mean by code? Perhaps
Application.WorkSheetFunction.Code(Range("A1"))
lenze
This is a discussion on VBA: how to check if the value of a cell is a number within the Excel Questions forums, part of the Question Forums category; Hi All, I am a newbie with VBA and I have 2 simple question...but I couldn't find the answers in ...
Hi All,
I am a newbie with VBA and I have 2 simple question...but I couldn't find the answers in the forum or by googling it
1) check if in cell A1 there is a number. Something like
2) how to get the "code" of cell A1 in VBA (i.e =code(A1)).I triedCode:If .Cells(1,1).Value is number then ....but it didn't work.Code:.Cell(1,1).Code
Many thanks for your help.
Peace
#1
IsNumeric(Cells(1,1))
#2
What do you mean by code? Perhaps
Application.WorkSheetFunction.Code(Range("A1"))
lenze
Last edited by lenze; Jul 22nd, 2009 at 07:27 PM.
If you have to tell your boss you're good with Excel, you're NOT!!
All I know about Excel I owe to my ignorance!
Scotch: Because you don't solve great Excel problems over white wine
Hi
1)
Can you be more specific?
Do you mean like the worksheet function IsNumber()?
Questions:
- If the cell has text convertible to a number like the string "123" do you want True or False?
- If the cell has a date, the worksheet function IsNumber() would give you a True. Is this what you want?
2)
I guess you mean the function Asc(). It can also be its big sister AscW() In case of a Unicode character.
Kind regards
PGC
To understand recursion, you must understand recursion.
Thank you for your answers!
pgc01,
- If it's a date I would like to get FALSE
- If I get a string "123" I would prefer TRUE (but if it's too complicated, FALSE will do it).
2) by code I mean the worksheet function =Code(), so I'm guessing that your suggestion Asc(Cell(1,1)) will do the job
Thank you guys
I have a related question:
1a) I would like to count how many numbers (IsNumeric() will do) different from 0 I have in a given range, say A1:A5.
In the worsheet I would write =SUMPRODUCT(--Isnumber(A1:A5), (A1:A5<>0)).
To find the numbers (even 0), as a first step, I tried
but didn't workCode:HowMany = Application.WorksheetFunction.SumProduct(--IsNumeric(rng))
Any suggestions?
Last edited by fab54; Jul 22nd, 2009 at 08:26 PM.
Use IsNumeric() as Lenze suggested. It accepts numbers and strings convertible to numbers and refuses dates.
Remark: it also accepts booleans, if you think it's relevant to your problem test against it.
P. S. Just saw your other post.
Instead of the worksheet function SumProduct() you can use a loop.
Last edited by pgc01; Jul 22nd, 2009 at 08:40 PM.
Kind regards
PGC
To understand recursion, you must understand recursion.
Thank you!
Managed to create the loop...yay!! (was quite easy though)
Great suggestions guys
Take into account that string "1D2" is recognized as numeric equal to 1*10^2 = 100.
Therefore IsNumeric("1D2") = True
To avoid it you can use: IsNumeric(Replace(ActiveCell, "D", "?")
The code below can help you:
Regards,Code:' Count the numerical values in the Rng range ' VBA usage: ' NumsCount(Range("A1:A5")) <-- Zeroes are not numerical ' NumsCount(Range("A1:A5"), True) <-- Zeroes are numerical ' Formula usage: =NumsCount(A1:A5) Function NumsCount(Rng As Range, Optional UseZero As Boolean) As Long Dim arr, v arr = Rng If Not IsArray(arr) Then ReDim arr(0): arr(0) = Rng For Each v In arr If IsNum(v) Then If UseZero Then NumsCount = NumsCount + 1 ElseIf v <> 0 Then NumsCount = NumsCount + 1 End If End If Next End Function ' The same as IsNumeric() but different for strings like "1D2", and skips the boolean values ' If NumOnly=True then strings are not recognised as numeric at all. ' If UseD=True then strings like "1D2" are recognised as numeric. Function IsNum(TxtOrNum, Optional NumOnly As Boolean, Optional UseD As Boolean) As Boolean Select Case VarType(TxtOrNum) Case 2 To 6, 14 ' Any type of numbers IsNum = True Case 8 ' vbString If Not NumOnly Then Dim d As Double If Not UseD Then If InStr(UCase(TxtOrNum), "D") > 0 Then Exit Function End If On Error Resume Next d = TxtOrNum IsNum = Err = 0 End If End Select End Function
Vladimir
Last edited by ZVI; Jul 22nd, 2009 at 09:23 PM.
Hi Vladimir
Good idea, your IsNum().
Maybe you want to deal also with another problem. The vba does not care about commas in expressions when it converts to numbers.
For example if in a cell you have "12,3,45" you would say it's a list, but vba and your IsNum() will say it's a valid number. Also for "1,23,4.5,67", if you assign it to a double you get 1234.567, the commas don't matter to vba, but you would not say that's a number.
In case of a string maybe the best is to use regular expressions and check the possible formats.
Kind regards
PGC
To understand recursion, you must understand recursion.
Hi PGC,
May be additional checking of comma in string is enough for solving the list recognising issue:
Regards,Code:' The same as IsNumeric() but different for strings like "1D2", and skips the boolean values ' If NumOnly=True then strings are not recognised as numeric at all. ' If UseD=True then strings like "1D2" are recognised as numeric. ' Comma in string means the list and recognised as not numerical Function IsNum(TxtOrNum, Optional NumOnly As Boolean, Optional UseD As Boolean) As Boolean Select Case VarType(TxtOrNum) Case 2 To 6, 14 ' Any type of numbers IsNum = True Case 8 ' vbString If Not NumOnly Then If InStr(TxtOrNum, ",") > 0 Then Exit Function ' <- Comma means the list Dim d As Double If Not UseD Then If InStr(UCase(TxtOrNum), "D") > 0 Then Exit Function End If On Error Resume Next d = TxtOrNum IsNum = Err = 0 End If End Select End Function
Vladimir
Last edited by ZVI; Jul 22nd, 2009 at 10:16 PM.
Like this thread? Share it with others