Re: Help with a macro/formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brefed15 - 19 Jun 2006 19:07 GMT

Perhaps this is a silly question, but i just can't figure out how to
format this worksheet. The problem is that a lot of the cells have
numbers in them with negative signs in the back instead of in the front
(ie. 23-) and i was wondering if anyone knew of a macro/formula i could
use to quickly fix this problem for all the cells. I would be extremely
grateful. Thanks!!!

Signature

brefed15

Reply to this Message

CLR - 19 Jun 2006 19:34 GMT

Select the column with the values having the minus sign following the number,
and run this code........use a copy of your data till you're sure all is well.

Sub ReverseMinus()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
If Right(cell, 1) = "-" Then
cell.Value = "-" & Mid(cell, Len(cell) - 1, 99)
End If
If Right(cell, 1) = "-" Then
cell.Value = Mid(cell, 1, Len(cell) - 1)
End If
Next
End Sub

Vaya con Dios,
Chuck, CABGx3

> Perhaps this is a silly question, but i just can't figure out how to
> format this worksheet. The problem is that a lot of the cells have
> numbers in them with negative signs in the back instead of in the front
> (ie. 23-) and i was wondering if anyone knew of a macro/formula i could
> use to quickly fix this problem for all the cells. I would be extremely
> grateful. Thanks!!!

Reply to this Message

Biff - 19 Jun 2006 19:48 GMT

I think your sub needs tweaked!

50- converts to 0
1- converts properly to -1
10- converts to 0
55- converts to -5

Biff

> Select the column with the values having the minus sign following the
> number,
[quoted text clipped - 23 lines]
>> use to quickly fix this problem for all the cells. I would be extremely
>> grateful. Thanks!!!

Reply to this Message

CLR - 19 Jun 2006 20:06 GMT

Right you are Sir.........that's what I get for not testing.........my
apologies to the OP.........this should be better....

Sub ReverseMinus()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
If Right(cell, 1) = "-" Then
cell.Value = "-" & Left(cell, Len(cell) - 1)
End If
Next
End Sub

Thanks for pointing that out Biff,
Vaya con Dios,
Chuck, CABGx3

> I think your sub needs tweaked!
>
[quoted text clipped - 32 lines]
> >> use to quickly fix this problem for all the cells. I would be extremely
> >> grateful. Thanks!!!

Reply to this Message

brefed15 - 19 Jun 2006 20:19 GMT

Thanks a lot Biff and Chuck!!!

Signature

brefed15

Reply to this Message

Biff - 19 Jun 2006 19:34 GMT

Hi!

One way:

Select the cells in question
Goto Data>Text to Columns
Just click straight through: Next>Next>Finish

That'll convert the TEXT entries like "23-" to negative numeric values -23.

Biff

Reply to this Message