| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Numbers To Text
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. -- Many Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbers To Text
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 "Hazel" wrote: 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. -- Many Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbers to Text
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbers to Text
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbers to Text
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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Numbers to Text
Hi
Absolutley brilliant worked like a dream -- Many Thanks "G Balamurugan" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
| VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
| Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
| How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Hybrid Mode
