Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Macro Error | Excel Discussion (Misc queries) | |||
change error message when no more for "find" in macro to find | Excel Programming | |||
ERROR 91 with FIND in macro | Excel Programming | |||
can't find error in macro logic... | Excel Programming | |||
macro error "can't find project or library" | Excel Programming |