Macro Error When It Can't Find Value

[tedd13]

I am using the following code to move the negative sign from the back of a
number to the front of the number:
For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
s = Trim(cell)
If IsNumeric(s) Then
cell.Value = CDbl(s)
End If
Next
This code works when there is a negative in the column. However, it does
not work when it can't find a negative. It gives me a "Run-time error
'1004': No cells were found".
Does anyone know how to code this so that the macro keeps going and dosn't
error out?
Thanks

 

[Jim Thomlinson]

Give this a try...

dim rngToConvert as range

on error resume next
set rngToConvert = Selection.SpecialCells(xlConstants, xlTextValues)
on error goto 0

if not rngtoconvert is nothing then
For Each cell In rngToConvert
s = Trim(cell)
If IsNumeric(s) Then
cell.Value = CDbl(s)
End If
Next
end if

 

[Jim Jackson]

If all else fails "On Error Resume Next"
Be sure to place "On Error Goto 0"
after the error-prone lines.

 

[JE McGimpsey]

Take a look here:

http://mcgimpsey.com/excel/postfixnegatives.html

 

[Paul]

Take a look here:

http://mcgimpsey.com/excel/postfixnegatives.html



- Show quoted text -

All you have to do to get your code to work is change
SpecialCells(xlConstants, xlTextValues) to SpecialCells(xlConstants)

 

[JE McGimpsey]

Not really. Using .SpecialCells(xlConstants) will still throw an error
if there are no constants in the range.