mass change of numbers to alphabetic

[Jeff]

I have a large spreadhseet with three groupings of numbers

1. less then 0 (this can be anything from -1 and below)
2. greater then 0 (this can be anything from 1 and above)
3. zero

I need to change items that are:

1.less then 0 to a Y,
2. greater then 0 to an N; and
3. 0 to NA.

I would really appreciate if someone could let me know how
this can be done.

Regards,

Jeff

 

[Don Guillett]

try this

Sub changenums()
For Each c In Selection
If c < 0 Then
c.Value = "Y"
ElseIf c > 0 Then
c.Value = "N"
Else
c.Value = "NA"
End If
Next
End Sub

 

[Peter Atherton]

Jeff

You can use a helper column and enter this formula in the
next column

=IF(A4<0,"Y",IF(A4>0,"N","N/A"))

or this macro will change the values directly working on
selected values

Sub Test()
Dim c As Variant
For Each c In Selection
If c < 0 Then
c.Value = "Y"
ElseIf c > 0 Then
c.Value = "N"
Else: c.Value = "N/A"
End If
Next
End Sub

Regards
Peter

 

[Jeff]

Perfect. thank you.

Jeff

 

[Don Guillett]

We say you are welcome

 

[SidBord]

I don't fully understand what you are doing. Are you
copying data from one cell to another, or are you inserting
a number into the cell and instead of the number you want
to see an alpha character? If you are copying the the
numbers from, say, A1 to B1, then you could use an IF
statement in B1: