CODEWITHSUNDEEP
excelvba
alexc
alexc

Reputation: 9

Check if cell contains numbers

If the cell within the range mentioned in the code contains a number e.g. "12" the code works and the cell becomes empty.

If the cell contains a number and text e.g. "12amasa" or "asa12" the code doesn't work.

I thought If IsNumeric(cell.Value) And cell.Value <> vbNullString Then would do the job, but it does not.

I want if the cell contains a digit, then it should be empty. Only letters from a-z allowed.

Dim cell As Range
Application.EnableEvents = False

For Each cell In Target
    If Not Application.Intersect(cell, Range("a5:a10000")) Is Nothing Then
        If IsNumeric(cell.Value) And cell.Value <> vbNullString Then              
            cell.Value = vbNullString
            cell.Interior.Color = RGB(255, 0, 0)
        Else
            cell.Interior.Color = RGB(1000, 1000, 1000)
        End If
    End If
Next cell

Application.EnableEvents = True

Upvotes: 1

Views: 2904

Answers (2)

Umair Mehmood
Umair Mehmood

Reputation: 36

This can also be achieved using regular expressions, and much helpful in more complicated cases:

Dim RE As Object, RE2 As Object
Set RE = CreateObject("VBScript.RegExp")
RE.ignorecase = True
RE.Global = True

RE.Pattern = "[a-z]*\d+[a-z]*" 'Case is ignored in this pattern, see above Re.ignorecase = True

For Each cell In target
     If Not Application.Intersect(cell, Range("a5:a10000")) Is Nothing Then
          If RE.test(cell.Value) Then
               cell.Value = vbNullString
               cell.Interior.Color = RGB(255, 0, 0)
          Else
               cell.Interior.Color = RGB(1000, 1000, 1000)
          End If
     End If
Next cell

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

You will need to loop through the characters checking for numeric values, or loop through the possible numeric values to see whether it is in the string.

Using the second method would be something like:

Dim cell As Range
Dim i As Long
Dim Matched As Boolean
Application.EnableEvents = False

For Each cell In Target
  If Not Application.Intersect(cell, Range("a5:a10000")) Is Nothing Then
     Matched = False
     For i = 0 To 9
       If Instr(CStr(cell.Value), CStr(i)) > 0 Then
         Matched = True
         Exit For
       End If
    Next
    If Matched Then              
      cell.Value = vbNullString
      cell.Interior.Color = RGB(255, 0, 0)
    Else
      cell.Interior.Color = RGB(1000, 1000, 1000)
    End If
  End If
Next cell

Application.EnableEvents = True

You can probably do this with a RegEx too, but I have never used them, so someone else will have to demonstrate that.

And, of course, I overlooked the most obvious answer ... the Like operator:

Dim cell As Range
Application.EnableEvents = False

For Each cell In Target
  If Not Application.Intersect(cell, Range("a5:a10000")) Is Nothing Then
    If CStr(cell.Value) Like "*[0-9]*" Then
      cell.Value = vbNullString
      cell.Interior.Color = RGB(255, 0, 0)
    Else
      cell.Interior.Color = RGB(1000, 1000, 1000)
    End If
  End If
Next cell

Upvotes: 1

Related Questions