Numbers To Text

  • Thread starter Thread starter Hazel
  • Start date Start date
H

Hazel

Hi

I wonder if you could help with a small macro - On sheet1 of my workbook
I have a rectangle in cell A2 and I need to assign a macro to it that would
do the following.

Range I2:I200 any number found in the range
would automatically change to the letter A.
Range J2:J200 the same as above the letter to be B.
Range K2:K200 the same as above the letter to be C.
Range L2:L200 the same as above the letter to be D.
Range M2:M200 the same as above the letter to be E.
Range N2:N200 the same as above the letter to be F.
Range O2:O200 the same as above the letter to be G.
There are blank cells in all the ranges.
 
Hazel

I don't understand what you mean by a rectangle in A2 but here's a macro
that will do what you want

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")
End If
Next
End Sub


Mike
 
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
 
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
 
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
Click to expand...

It's a problem with copying the code from a Usenet posting; you
sometimes pick up unintended line breaks. Make sure that you get rid
of the line break between the last 0, and the "A" so that it all
appears on the one line.
 
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
 
Oh, and of course, we could do away with external functions like these and
simply reach into the Worksheet Functions for the IsNumber function and use
that. For example, put an entry in A1 and try this...

MsgBox WorksheetFunction.IsNumber(Range("A1").Value)

--
Rick (MVP - Excel)


Rick Rothstein said:
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 said:
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
Click to expand...
Click to expand...
 
Assuming your entries are all constants (that is, the cells in I2:O200 do
not contain formulas), here is a much simpler macro to do what you want...

Sub Text2Numbers()
Dim C As Range
For Each C In Range("I2:O200").SpecialCells( _
xlCellTypeConstants, xlNumbers)
C.Value = Chr$(C.Column + 56)
Next
End Sub
 
I guess to be "safe" (that is, to prevent any errors if the range does not
have any numbers in it), we should add an On Error trap to the macro...

Sub Text2Numbers()
Dim C As Range
On Error Resume Next
For Each C In Range("I2:O200").SpecialCells( _
xlCellTypeConstants, xlNumbers)
C.Value = Chr$(C.Column + 56)
Next
End Sub
 
Back
Top