IsNumeric

[VegasNath]

Hi,

I am using the following to identify if the first 11 characters of a string are numbers.

If Not rng.Offset(0, 1) = "10" And Not IsNumeric(Left(rng.Value, 11))

This works as desired, except for "-", the minus sign is treated as numeric (I think I understand why), but I need it not to be. I only want to ensure that they are actual numbers. Is there a more accurate test that I can use?

[StuartR]

I assume you are using IsNumber (I am not familiar with the worksheet function IsNumeric)

IsNumber only accepts a - at the beginning of a number, so you could check that the number is greater than zero.
Are you happy to accept numbers that start with a + sign, or that have a . in the middle, or that have , between groups of digits?

[VegasNath]

Hi Stuart,

The syntax that I am using (inside vba) is IsNumeric.

My test really needs to ignore all signage, my interest is to ascertain if the first 11 characters are actually in the range 0 to 9.

[HansV]

IsNumeric will return True for a string that starts or ends with a plus or minus sign, and apart from that contains only digits. It'll return False if the string contains a plus or minus sign in any other position. So you could check the first and last characters separately. Or you could loop through the characters:

Code: Select all

Dim blnNumeric As Boolean
Dim i As Integer
Dim strValue As String
blnNumeric = True
strValue = Left(rng.Value, 11)
For i = 1 To 11
  Select Case Asc(Mid(strValue, i, 1))
    Case 48 To 57
      ' OK
    Case Else
      blnNumeric = False
      Exit For
  End Select
Next i
If Not rng.Offset(0, 1) = "10" And blnNumeric = False Then
  ...

[VegasNath]

Hi Hans,

Thanks for your help.

I am getting a run time error '5' Invalid procedure call or arguement on the line > Select Case Asc(Mid(strValue, i, 1))

I'm not sure what's wrong?

Code: Select all

'Evaluate the first 11 characters of Column C, if not numeric, Convert to CP10
    Set dstrg = dstws2.Range("C2:C" & dstlr)
    
    For Each rng In dstrg
        Dim blnNumeric As Boolean
        Dim i As Integer
        Dim strValue As String
        blnNumeric = True
        strValue = Left(rng.Value, 11)
        For i = 1 To 11
          Select Case Asc(Mid(strValue, i, 1))
            Case 48 To 57
              ' OK
            Case Else
              blnNumeric = False
              Exit For
          End Select
        Next i
        If Not rng.Offset(0, 1) = "10" And blnNumeric = False Then
            rng.Offset(0, 1) = "'10"
            rng.Offset(0, 1).Font.Bold = True
            cnt = cnt + 1
        End If
    Next rng

[HansV]

The error occurs if rng.Value is less than 11 characters long. You can get around it by changing the line

For i = 1 To 11

to

For i = 1 To Len(strValue)

[VegasNath]

Thanks, That fixed it.