The code will run on any sheet that is active.

Do you want to run it on multiple selected sheets or all sheets in the workbook?

Sub RemoveCR()
Dim R As Range
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
'If only some sheets are selected change the above line to
'For each ws in ActiveWindow.SelectedSheets
For Each R In ws.Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next R
Next ws
End Sub


Gord Dibben MS Excel MVP

On Thu, 16 Aug 2007 08:42:00 -0700, wrote:

On Aug 15, 4:47 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Tim has given you a method to do it with a spreadsheet formula coupled with
a help column. However, if you need to physically change the values in their
current cells, then this macros should do that for you (just set the
appropriate range in the For Each statement line)..

Sub RemoveCR()
Dim R As Range
For Each R In Range("A1:A200")
If R.Value Like "*[Cc][Rr]" Then
R.Value = -Left(R.Value, Len(R.Value) - 2)
End If
Next
End Sub

Rick

wrote in message

ps.com...



The program I am using is placing a "CR" at the end of every negative
number to denote that it is a "credit". This poses a problem when
using the data, as Excel won't recognize the number. I need to get/
write a macro that deletes the "CR" and makes the number negative. I
tried the "Left" commands in excel, but it won't work since you have
to specify how many numbers numbers are to the left and that changes
depending on the size of the number. Any ideas?- Hide quoted text -

- Show quoted text -

I used this, and tried to add the ActieSheet command to apply to
multiple worksheets, but its not working for me. I've looked at the
code several times. Ideas?