Macro Error When It Can't Find Value
 

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

Macro Error When It Can't Find Value
 

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
--
HTH...

Jim Thomlinson


"tedd13" wrote:

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

Macro Error When It Can't Find Value
 

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

Jim


"tedd13" wrote:

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

Macro Error When It Can't Find Value
 

Take a look he

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

In article ,
tedd13 wrote:

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

Macro Error When It Can't Find Value
 

On Mar 30, 8:43 am, JE McGimpsey wrote:
Take a look he

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

In article ,



tedd13 wrote:
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- Hide quoted text -

- Show quoted text -

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

Macro Error When It Can't Find Value
 

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

In article .com,
"Paul" wrote:

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