I'd use something like:
Option Explicit
Sub MakePosNeg()
Dim myCell As Range
Dim Rng As Range
Set Rng = Nothing
On Error Resume Next
Set Rng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlNumbers))
On Error GoTo 0
If Rng Is Nothing Then
MsgBox "No constant numbers found in selection."
Exit Sub
End If
For Each myCell In Rng.Cells
If myCell.Value > 0 Then
myCell.Value = -myCell.Value
End If
Next myCell
End Sub
That .specialcells stuff does the same thing as selecting a larger range and
hitting F5 (or Edit|Goto in xl2003 menus). Then Special, then Constants and
Numbers (and unchecking all those other options.
That means you don't have to be as careful selecting the range and it'll be a
little faster--it'll have fewer cells to loop through (usually).
Dave,
You are correct; the generic range does include text. Your suggestion
works. However because A1:ZZ10000 is a generic range, all non-numeric fields
change.
Is there a way to preselect a range (for example E6:M100 or G2:K2000) before
running the generic macro to change pos to neg?
I realize it's safer to go from neg to pos with a generic macro. But we
have some instances where we need to fix data entered wrong (chg pos to neg).