ExcelBanter - View Single Post

On 30 Nov 2005 02:38:55 -0800, wrote:

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