|
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 |
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 |
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 |
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 |
Numbers to Text
On Sun, 30 Aug 2009 03:03:06 -0700, 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 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. |
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 |
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 |
Numbers to Text
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 -- Rick (MVP - Excel) "Hazel" . wrote in message ... 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 |
Numbers to Text
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 -- Rick (MVP - Excel) "Hazel" . wrote in message ... 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 |
| All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com