Finding cells with a number and replacing with an "X"

[srhilvers]

Hello...
I am trying to create a routine which finds all cells with a numerical value (no text or special characters) and replace those cells with an "X". I believe all the cells are formatted as "General".
Any pointers on how to do this?
Thank you,

 

[btadams]

Welcome to the board!

Select the range (or entire worksheet) and press F5 to open the GoTo dialog. Click the Special button and select Constants, deselecting Text, Logicals, Errors. Then type in X and press Ctrl +Enter

 

[VoG]

Try

Sub NosToX()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    With c
        If IsNumeric(.Value) Then .Value = "X"
    End With
Next c
End Sub

 

[taigovinda]

Welcome to the board!

A simple way to do it is to select all the cells you want to affect:
Goto Special (press control+G, alt+S)
Choose Constants
Check only the box for Numbers (press x,g,e to uncheck the other three)
Choose OK (press enter)
...you are now selecting all of the numbers - including dates
Press X
Press control+enter to apply the X to all of the selected cells.

In this sheet, only the second cell would not be changed to an X; the date is considered a number by Excel:

Excel Workbook
	A
1	1
2	text
3	15
4	1/8/2010
Sheet1

Excel 2003



Here is a macro to do the same thing (select the cells, then run the macro if you don't want to do control+g,alt+s,o,x,g,e,enter,X,control+enter):

Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/8/2010
'
'
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.FormulaR1C1 = "X"
End Sub

Hope that helps!

Tai

edit: I wrote this myself, I swear I didn't copy post #2 and #3

 

[srhilvers]

Awesome....exactly what I was looking for.
THANK YOU!