Hi there,

I have a values + or - in one column and the number in the next

What I want to do is add the + or - to the number i.e.

- in one column, 10 in the next you get -10

I know I can do this by using the & function but this is not the way I want
to do it

I don't want it to lookup, I would like it to be permament, and it must
totally replace the value 10 with -10 as in the above example

I hope this is not clear as mud, Hope someone can help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

One way:

A1: 10
B1: =-A1

In article <5b144ff31badd@uwe,
"Danny Boy via OfficeKB.com" <u15117@uwe wrote:

Hi there,

I have a values + or - in one column and the number in the next

What I want to do is add the + or - to the number i.e.

- in one column, 10 in the next you get -10

I know I can do this by using the & function but this is not the way I want
to do it

I don't want it to lookup, I would like it to be permament, and it must
totally replace the value 10 with -10 as in the above example

I hope this is not clear as mud, Hope someone can help

You can still use the & function then Copy/Paste Special Values over your
numbers. This will give you the look you desire, but they will not be
numbers, they will be text, and therefore cannot perform mathematics with
them. You can convert them to numbers with the Value() function, but then
you would lose the "+" with positive numbers. The "-" will remain on
negative numbers. The "+" in front of a number will render the number as
text and useless for calculations unless the Value() function is used on it.
You can't have it both ways with Positive numbers.

Mike F
"Danny Boy via OfficeKB.com" <u15117@uwe wrote in message
news:5b144ff31badd@uwe...
Hi there,

I have a values + or - in one column and the number in the next

What I want to do is add the + or - to the number i.e.

- in one column, 10 in the next you get -10

I know I can do this by using the & function but this is not the way I
want
to do it

I don't want it to lookup, I would like it to be permament, and it must
totally replace the value 10 with -10 as in the above example

I hope this is not clear as mud, Hope someone can help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Thanks for your responses

I don't have the option to use any of those methods,

I have cell A1 = 10 and B1 = -

This must change A1 to -10

I have no other option, I cannot use any other cells for joining,

Any others Ideas?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

One way:

Public Sub JoinNegs()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If .Offset(0, 1).Text = "-" Then _
.Value = -.Value
End With
Next rCell
End Sub

In article <5b153e053bbd9@uwe,
"Danny Boy via OfficeKB.com" <u15117@uwe wrote:

Thanks for your responses

I don't have the option to use any of those methods,

I have cell A1 = 10 and B1 = -

This must change A1 to -10

I have no other option, I cannot use any other cells for joining,

Any others Ideas?