How to check if the range of cell contains any character

[dmadhup]

I wrote this sub:

Sub TextToNumbe()


Dim rCell As Range
Dim sMyString As String
Dim LastRowIndex As Long
LastRowIndex = Cells(Rows.Count, "A").End(xlUp).Row




On Error GoTo ErrorHandle




With Sheets("Sheet1").Range("A1:A10")
    .NumberFormat = "General"
    .Value = .Value
End With


Sheets("Sheet1").Select




'Range("F6").Formula = "=SUM(COUNTIF(A1:A10," _
'& "{""A"",""B"",""C"",""D"",""E"",""F"",""G"",""H"",""I"",""J"",""K"",""L""," _
'& """M"",""N"",""O"",""P"",""Q"",""R"",""S"",""T"",""U"",""V"",""W"",""X"",""Y"",""Z""," _
'& """!"",""@"",""#"",""$"",""%"",""^"",""&"",""*"",""/"","">"",""<"","","",""~"","";"","":""," _
'& """?"",""-"",""+"",""("","")""}))"






Range("F6").Formula = "=SUM(COUNTIF(A1:A & LastRowIndex," _
& "{""A"",""B"",""C"",""D"",""E"",""F"",""G"",""H"",""I"",""J"",""K"",""L""," _
& """M"",""N"",""O"",""P"",""Q"",""R"",""S"",""T"",""U"",""V"",""W"",""X"",""Y"",""Z""," _
& """!"",""@"",""#"",""$"",""%"",""^"",""&"",""*"",""/"","">"",""<"","","",""~"","";"","":""," _
& """?"",""-"",""+"",""("","")""}))"




Set rCell = Range("A1:A" & LastRowIndex)


If Range("F6").Value > 0 Then
    MsgBox "Contains special character in range [ " & rCell.Address & " ]. Please check and try again.", vbInformation, "Data Validation"
End If




Done:
    Exit Sub
    
ErrorHandle:
MsgBox Err.Description & " Error in procedure CellCheck."


End Sub

But I have problem on this line:
Range("F6").Formula = "=SUM(COUNTIF(A1:A & LastRowIndex,"

Where A1 :A & LastRowIndex is not true. If I hardcode A1:A10 then it works. But I want to put LastRowIndex so that program can find the end value.

Any help is appreciated.
Thanks

 

[Scott Huish]

The problem with the period counting is because of the wildcard characters. Rick your formula will somehow need to contain ~? and ~* to work properly.

 

[Rick Rothstein]

Hi Scott,

The purpose of code I have posted is to detect the presence of any character in the column.

If the purpose of the code is to detect the presence of any character rather than the specific characters you listed in your original post, then you can do use this...

Range("F7").Formula = "=COUNTIF(A1:A" & LastRowIndex & ",""*?"")"

 

[dmadhup]

My bad. I mean to say presence of any character except period like "."
Thank you

 

[Scott Huish]

Perhaps:

Range("F7").Formula = "=COUNTIF(A1:A" & LastRowIndex & ",""*?"")-COUNTIF(A1:A" & LastRowIndex & ",""*.*"")"

 

[Rick Rothstein]

Perhaps:

Range("F7").Formula = "=COUNTIF(A1:A" & LastRowIndex & ",""*?"")-COUNTIF(A1:A" & LastRowIndex & ",""*.*"")"

I was thinking of that approach also, but decided against it because the OP said "like '.'"... that coupled with the restricted character set originally posted leads me to think we need to stick to the original set of characters shown in Message #1 . I am not sure what the OP wants to do can be done with normal Excel functions... I think a UDF (user defined function) may be the needed approach.

 

[Rick Rothstein]

... I think a UDF (user defined function) may be the needed approach.

And here is such a UDF (it returns TRUE if at least one of the specified characters is somewhere within a cell in the specified range)...

[table="width: 500"]
[tr]
	[td]Function HasChar(Rng As Range) As Boolean
  ' Char Set: ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*/><,~;:?-+()
  Dim Cell As Range
  For Each Cell In Rng
    If Cell.Value Like "*[#-&(-,/:-<>-Z^~!-]*" Then
      HasChar = True
      Exit Function
    End If
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use HasChar just like it was a built-in Excel function. For example,

=HasChar(A1:A100)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[dmadhup]

Hi Rick,
It works. I am used to with UDF's and Macros.
I am trying to understand the pattern "*[#-&(-,/:-<>-Z^~!-]*" and how it works.

Actually, I am using your provided code like below so that I use the function multiple times.

Function HasChar() As Boolean
Dim LastRowIndex As Long
Dim Rng As Range
LastRowIndex = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A1:A" & LastRowIndex)
  ' Char Set: [EMAIL="ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*/"]ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*/[/EMAIL]><,~;:?-+()
  Dim Cell As Range
  For Each Cell In Rng
    If Cell.Value Like "*[#-&(-,/:-<>-Z^~!-]*" Then
      HasChar = True
      Exit Function
    End If
  Next
End Function

Sub CheckChar()
If HasChar = True Then
      MsgBox "Contains Character"
End If
End Sub

 

[Scott Huish]

The = TRUE part isn't necessary, you can do all of that in one line:

Sub CheckChar()
If HasChar Then MsgBox "Contains Character"
End Sub

 

[dmadhup]

Great!
Thank you all for help