Formula to replace nos. below 100??

[cricketunes]

Hey folks,
am new to this forum. I have a 1000 X 100 spreadsheet with numbers on
it. I would like to replace all the negative numbers with the value 5
and all postive numbers greater than 100 with the value 100. Is there a
way I can do this quickly?

Appreciate your ideas,
Cricket

 

[Ron Rosenfeld]

Hey folks,
am new to this forum. I have a 1000 X 100 spreadsheet with numbers on
it. I would like to replace all the negative numbers with the value 5
and all postive numbers greater than 100 with the value 100. Is there a
way I can do this quickly?

Appreciate your ideas,
Cricket

If you just need the display to change, but the contents remain unchanged, then
you can use the custom format [<0]5;[>100]"100";0

Format/Cells/Number/Custom Type: [<0]5;[>100]"100";0



If you actually want the numbers to be changed, then backup your data first.

<alt-F11> opens the VB Editor
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

Return to your worksheet and select the area of cells to be changed.

<alt-F8> opens the Macro Dialog Box.

Select ChangeNum and Run.

=============================
Sub ChangeNum()
Dim c As Range

For Each c In Selection
If IsNumeric(c.Text) Then
If c.Value < 0 Then c.Value = 5
If c.Value > 100 Then c.Value = 100
End If
Next c

End Sub
============================


--ron

 

[cricketunes]

Worked like a dream!!
Thank you Ron, you;re a genius

 

[Ron Rosenfeld]

Worked like a dream!!
Thank you Ron, you;re a genius

Glad to help. Thank you for the feedback.
--ron