Your IsThisNumeric function will return True for some non-numeric entries.
For example, this "1...2---3", or this ".", or this "-", and so on. Here is
a bullet-proof function I developed several years ago when I was
volunteering answering questions over in the compiler version of Visual
Basic newsgroups and which works just as well here in the VBA world...
Function IsNumber(ByVal Value As String) As Boolean
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) 0 And Value < "." And _
Value < vbNullString
End Function
--
Rick (MVP - Excel)
"G Balamurugan" <G
wrote in message
...
Hazel, please try this code.
Sub ConvNumb2Text()
On Error Resume Next
Dim i As Integer
For i = 2 To 200
If IsThisNumeric(Range("I" & i).Value) Then Range("I" & i).Value = "A"
If IsThisNumeric(Range("J" & i).Value) Then Range("J" & i).Value = "B"
If IsThisNumeric(Range("K" & i).Value) Then Range("K" & i).Value = "C"
If IsThisNumeric(Range("L" & i).Value) Then Range("L" & i).Value = "D"
If IsThisNumeric(Range("M" & i).Value) Then Range("M" & i).Value = "E"
If IsThisNumeric(Range("N" & i).Value) Then Range("N" & i).Value = "F"
If IsThisNumeric(Range("O" & i).Value) Then Range("O" & i).Value = "G"
Next i
End Sub
Private Function IsThisNumeric(strValue As String) As Boolean
Dim j As Integer
If strValue = "" Then Exit Function
IsThisNumeric = True
For j = 1 To Len(strValue)
If Not InStr(1, "0123456789.-", Mid(strValue, j, 1)) 0 Then
IsThisNumeric = False
Exit For
End If
Next j
End Function
"Hazel" wrote:
Hi Mike
Thanks for the quick reply having a problem with your macro
Sub sonic()
Set MyRange = Range("I2:o200")
For Each c In MyRange
If IsNumeric(c) And Len(c) 0 Then
!!!c.Value = WorksheetFunction.Choose(c.Column, 0, 0, 0, 0, 0, 0, 0, 0,
"A", "B", "C", "D", "E", "F", "G")!!! fonts on these two lines all in red
'am I missing something???
End If
Next
End Sub
--
Many Thanks