Forum Discussion
Excel VBA
I am having trouble getting my code to look at the value of cell A1 instead of the text. Any help would be appreciated. 🙂 Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cel...
Use WorksheetFunction.IsNumber instead of VBA IsNumeric.
Note that IsNumeric("123") returns True. As VBA help explains: ``Returns a Boolean value indicating whether an expression can be evaluated as a number``.
In contrast, WorksheetFunction.IsNumber (i.e. Excel ISNUMBER) returns TRUE only if the parameter is a number.
PS.... But in fact, your mistake might be that the Excel value is text, not a number. I cannot read the forumla in the image in your posting. But perhaps you should convert the result to a number. There are many ways. For example, --RIGHT(....), 1*RIGHT(....), VALUE(RIGHT(....)).
And perhaps your Excel formula is wrong, in the first place. If the referenced cell contains numeric Excel time -- that is, ISNUMBER returns TRUE -- using a function like RIGHT is not the correct way to extract the hour, minute or second. Use HOUR, MINUTE and SECOND functions instead. (Although HOUR will not return time that is 24 hours or more.) Test with the time 12:34:56 in A1. Note that =A1 formatted as Number displays the decimal value 0.524259259259259. Therefore, RIGHT(A1,8) returns "0.524259", not "12:34:56".
Nevertheless, I still would not use IsNumeric, since that does not seem to do what you expect.