Conflict with "Right" / "Left" Command

[DJB]

Hi

I use this routine to convert trailing minus signs to “proper” minus signs. It worked fine as a macro. I then incorporated this into an add-in that I use to clean up imported numbers at work. Still fine. As I’ve added more code to this utility it has stopped working, but will still work independently as a macro. The problem seems to lie with the “Right” instruction. Does anybody know of any conflict issues with the Left and Right commands

The error I get is :
Compile error : Wrong number of arguments or invalid property assessment

For Each UsrRng In Selection
If Right(UsrRng, 1) = "-" Then
NewNum = Left(UsrRng, Len(UsrRng) - 1)
UsrRng.Formula = "-" & NewNum
End If
Next UsrRng

Massive thanks to anyone who can shed any light on this for me.

DJB

[Ron de Bruin]

Hi DJB

Here are two examples

Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' Use of CDbl suggested by Peter Surcouf
' Program by Dana DeLouis, [email protected]
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim bigrng As Range

On Error Resume Next
Set bigrng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If bigrng Is Nothing Then Exit Sub

For Each rng In bigrng.Cells
rng = CDbl(rng)
Next
End Sub


Public Sub ConvertPostNegatives()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
cell.Value = CDbl(cell.Value)
Next cell
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DJB" <[email protected]> wrote in message news:[email protected]...
>
> Hi
>
> I use this routine to convert trailing minus signs to "proper" minus
> signs. It worked fine as a macro. I then incorporated this into an
> add-in that I use to clean up imported numbers at work. Still fine.
> As I've added more code to this utility it has stopped working, but
> will still work independently as a macro. The problem seems to lie
> with the "Right" instruction. Does anybody know of any conflict issues
> with the Left and Right commands
>
> The error I get is :
> Compile error : Wrong number of arguments or invalid property
> assessment
>
> For Each UsrRng In Selection
> If Right(UsrRng, 1) = "-" Then
> NewNum = Left(UsrRng, Len(UsrRng) - 1)
> UsrRng.Formula = "-" & NewNum
> End If
> Next UsrRng
>
> Massive thanks to anyone who can shed any light on this for me.
>
> DJB
>
>
> --
> DJB
> ------------------------------------------------------------------------
> DJB's Profile: http://www.excelforum.com/member.php...o&userid=21221
> View this thread: http://www.excelforum.com/showthread...hreadid=400801
>

[DJB]

Hi Ron

Thanks so much for the alternative routines. They work just fine.

Don't suppose you have any idea of what caused the conflict in the first instance...?

Thanks again

DJB

[Ron de Bruin]

Maybe if we see the other code

But use the code I posted because it is faster and will also work if you have spaces in the cells

Is the macro I posted working in the Add-in ???


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DJB" <[email protected]> wrote in message news:[email protected]...
>
> Hi Ron
>
> Thanks so much for the alternative routines. They work just fine.
>
> Don't suppose you have any idea of what caused the conflict in the
> first instance...?
>
> Thanks again
>
> DJB
>
>
> --
> DJB
> ------------------------------------------------------------------------
> DJB's Profile: http://www.excelforum.com/member.php...o&userid=21221
> View this thread: http://www.excelforum.com/showthread...hreadid=400801
>

[DJB]

Hi Ron

The complete add-in has over 1000 lines of code (it's grown organically over the last 2 years) and a dozen user forms so I shan't bore with the details of it - just accept that somewhere over the last 24 months I've added a module (perhaps an API call or something) that doesn't get along with the Left and Right instructions.

Both pieces of code you gave me work just perfect within the add-in - and both better and faster than my code. Ahh well...

Thanks again for the assistance.

Take care

DJB