Thanks Gord,
I had tried that and kept getting the reply "no cells were found". So I
added "ActiveWorkbook", hoping to fix it. Same result.
This is my code:
Set rngRR = ActiveWorkbook.Sheets("UC GST Lgr SUM").Range("I11:I12") _
.SpecialCells(xlCellTypeConstants, _
xlTextValues)
I hate to be a pest but would very much appreciate if you could identify my
error.
Cheers
Jim
:
Jim
Just hard-code the Sheet and Range.
Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Sheets("Sheet1").Range("E1:H20") _
..SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub
Gord
On Mon, 12 Dec 2005 00:37:05 -0800, "REcord deleted error using parameter que"
Hi Gord,
I don't mean to hijact this thread but your macro may be just what I'm
looking for. However, I am uncertian where to enter the details of the sheet
and range if the data to be modified is always in the same sheet and range
(other than the sumary sheet that summarises the data). In otehr words I
don't want to have to select the ranges to be changed each time.
I would appreciate any help you can offer. If necessary I will start a new
thread.
Cheers
Jim
:
Scott
This macro will remove all but numbers and decimal point(if one present)
Also strips spaces. Select range or column first then run.
Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord Dibben Excel MVP
On Mon, 23 May 2005 10:41:18 -0700, SWBodager
How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager (
[email protected])