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

 

[jproffer]

I don't have a way to test it, but the quotes don't look right to me. Maybe try this:

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""," _
& """!"",""@"",""#"",""$"",""%"",""^"",""&"",""*"",""/"","">"",""<"","","",""~"","";"","":""," _
& """?"",""-"",""+"",""("","")""}))"

Well my spacing got messed up, but I didn't change anything except this: Range("F6").Formula = "=SUM(COUNTIF(A1:A" & LastRowIndex &

 

[dmadhup]

Thank you jProffer. It works.

 

[Rick Rothstein]

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""," _
& """!"",""@"",""#"",""$"",""%"",""^"",""&"",""*"",""/"","">"",""<"","","",""~"","";"","":""," _
& """?"",""-"",""+"",""("","")""}))"

You might find it interesting that the above code line can be replaced with this one...

[table="width: 500"]
[tr]
	[td]Range("F7").Formula = "=SUM(COUNTIF(A1:A" & LastRowIndex & ",{""" & Replace(Left(StrConv( _
                      "ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*/><,~;:?-+()", _
                      vbUnicode), [COLOR="#0000FF"][B]91[/B][/COLOR]), Chr(0), """,""") & """}))"[/td]
[/tr]
[/table]

Note: The hard-coded 91 toward the end of the code line is the length of the quoted text string minus one (it gets rid of the trailing Chr(0) that the StrConv function introduces).

 

[dmadhup]

Thank you Rick.
As we have not included "." character in string set. However the code still detecting it. Is there any way I can ignore "."

 

[Rick Rothstein]

Thank you Rick.
As we have not included "." character in string set. However the code still detecting it. Is there any way I can ignore "."

I am not 100% sure of what the formula placed in cell F7 is supposed to to be returning, so I cannot vouch for its accuracy. All I did was look at the code line jproffer posted in Message #2 an realized that I could create a more compact code line the same formula in cell F7 than his code did. If my code line's result in Message #2 fails with a dot, then I would expect jproffer's code line to also fail in the same way because, as far as I can tell, the code line I posted in Message #4 produces the identical formula in cell F7 that the code line jproffer posted in Message #2 does. Again, I cannot vouch for the results of that formula.

 

[dmadhup]

Yes Rick. Both code giving the same result. Thank you for making compact code and making nicer look.

 

[Scott Huish]

What is the code supposed to accomplish exactly and then maybe we can fix it.

 

[dmadhup]

Hi Scott,

The purpose of code I have posted is to detect the presence of any character in the column. Below code (By Rick) put greater than zero value if there is any character in Column A.
And if F7 cells contain greater than zero then we can say there is the presence of a character in Column A.

 

[Rick Rothstein]

You might find it interesting that the above code line can be replaced with this one...

[table="width: 500"]
[tr]
	[td]Range("F7").Formula = "=SUM(COUNTIF(A1:A" & LastRowIndex & ",{""" & Replace(Left(StrConv( _
                      "ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*/><,~;:?-+()", _
                      vbUnicode), [COLOR="#0000FF"][B]91[/B][/COLOR]), Chr(0), """,""") & """}))"[/td]
[/tr]
[/table]

Note: The hard-coded 91 toward the end of the code line is the length of the quoted text string minus one (it gets rid of the trailing Chr(0) that the StrConv function introduces).

What I highlighted in red above was supposed to have said... "twice the length of the quoted text string minus one".