How to convert positive figures to negative ones?

[mviuya]

How do we convert positive numbers into negative ones under one column?

 

[Mike H]

Hi,

Put -1 in a cell and copy it

Select your column of positive numbers then
edit|Paste special
select multiply
Click OK
delete the cell with -1 in

Mike

 

[Peo Sjoblom]

Put -1 in an empty cell, format it the same way as the others, copy it,
select the range with negative numbers and do edit>paste special and select
multiply

If the numbers are in the same column you can use a help column

=IF(A1>0,-A1,A1)

copy down as long as needed, copy and paste special as values in place
and delete or copy and paste over the old column

--


Regards,


Peo Sjoblom

 

[Marcelo]

hi,

you can use an auxiliar cell type there "-1" (no quotes) copy it.

Select the numbers you would like to change and

and paste special Values - Multiply

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"mviuya" escreveu:

 

[Héctor Miguel]

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[mviuya]

Hi Marcelo,
That was simple and helpful.
Thanks a lot.

M Viuya from the USA

 

[Billyruben]

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[Dave Peterson]

I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim Var1 As Long

Var1 = 30
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = -1
myCell.Copy
.Range("f4:F" & Var1).PasteSpecial _
operation:=xlPasteSpecialOperationMultiply
myCell.ClearContents
End With
End Sub

This is the same thing as putting -1 in an empty cell and then edit|copy that
cell.

Then select the range to adjust
Edit|paste special|multiply
and clearing that helper cell.

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[Billyruben]

Thanks Dave,

It works like a charm! I don't have the slightest about the code, so I have
to store it in my "Tool Box" and just know that if I set it up right, I can
use it any time I face the same situation.

I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim Var1 As Long

Var1 = 30
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = -1
myCell.Copy
.Range("f4:F" & Var1).PasteSpecial _
operation:=xlPasteSpecialOperationMultiply
myCell.ClearContents
End With
End Sub

This is the same thing as putting -1 in an empty cell and then edit|copy that
cell.

Then select the range to adjust
Edit|paste special|multiply
and clearing that helper cell.

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[Dave Peterson]

I think doing it manually is much quicker than finding the macro, installing it,
modifying it for the correct range, and then running it.

Thanks Dave,

It works like a charm! I don't have the slightest about the code, so I have
to store it in my "Tool Box" and just know that if I set it up right, I can
use it any time I face the same situation.

I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim Var1 As Long

Var1 = 30
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = -1
myCell.Copy
.Range("f4:F" & Var1).PasteSpecial _
operation:=xlPasteSpecialOperationMultiply
myCell.ClearContents
End With
End Sub

This is the same thing as putting -1 in an empty cell and then edit|copy that
cell.

Then select the range to adjust
Edit|paste special|multiply
and clearing that helper cell.

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

:

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[Billyruben]

Hi Dave, I'll agree with you if we are talking about an occasional use of
it. What I didn't share with you is that procedure is but one step in a long
series of steps within a process which will be used several times during each
month. I worded my question in such a way as to not get the issue buried
within the bigger picture. I don't actually program so given that this would
be an ongoing routine, I captured each Excel step using the Macro Recorder.
I then proceeded to string all those macros together into one small,
functional program. A lot of the modifications were intuitive or logical,
but there have been three questions involving syntax that have made me yell
"uncle." Fortunately you folks out in the community have been more than
helpful in reponding to my requests for assistance. Keep an ear to the
ground, you'll probably see more questions posted by me. Thanks for being
out there to help non-programmers.

I think doing it manually is much quicker than finding the macro, installing it,
modifying it for the correct range, and then running it.

Thanks Dave,

It works like a charm! I don't have the slightest about the code, so I have
to store it in my "Tool Box" and just know that if I set it up right, I can
use it any time I face the same situation.

I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim Var1 As Long

Var1 = 30
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = -1
myCell.Copy
.Range("f4:F" & Var1).PasteSpecial _
operation:=xlPasteSpecialOperationMultiply
myCell.ClearContents
End With
End Sub

This is the same thing as putting -1 in an empty cell and then edit|copy that
cell.

Then select the range to adjust
Edit|paste special|multiply
and clearing that helper cell.

Billyruben wrote:

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

:

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.

 

[Dave Peterson]

I see.

And it makes perfectly good sense to automate it when it's part of a larger
procedure.

Hi Dave, I'll agree with you if we are talking about an occasional use of
it. What I didn't share with you is that procedure is but one step in a long
series of steps within a process which will be used several times during each
month. I worded my question in such a way as to not get the issue buried
within the bigger picture. I don't actually program so given that this would
be an ongoing routine, I captured each Excel step using the Macro Recorder.
I then proceeded to string all those macros together into one small,
functional program. A lot of the modifications were intuitive or logical,
but there have been three questions involving syntax that have made me yell
"uncle." Fortunately you folks out in the community have been more than
helpful in reponding to my requests for assistance. Keep an ear to the
ground, you'll probably see more questions posted by me. Thanks for being
out there to help non-programmers.

I think doing it manually is much quicker than finding the macro, installing it,
modifying it for the correct range, and then running it.

Thanks Dave,

It works like a charm! I don't have the slightest about the code, so I have
to store it in my "Tool Box" and just know that if I set it up right, I can
use it any time I face the same situation.

:

I'd do something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim Var1 As Long

Var1 = 30
With ActiveSheet
Set myCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
myCell.Value = -1
myCell.Copy
.Range("f4:F" & Var1).PasteSpecial _
operation:=xlPasteSpecialOperationMultiply
myCell.ClearContents
End With
End Sub

This is the same thing as putting -1 in an empty cell and then edit|copy that
cell.

Then select the range to adjust
Edit|paste special|multiply
and clearing that helper cell.

Billyruben wrote:

Hola Hector,

What is wrong with the following:

Var1 = 30

[F4:F&Var1] = [F4:F&Var1*-1]

Note1: all values are positive
Note2: using F30 instead of F&Var1 works, but the column list will always be
different. (Yes, the actual variable is "Dim"ed and uses a routine to find
the last row.)

Question: what is wrong with the syntax? What is the proper way of
combining F with Var1? F&Var1 does not work.

Saludos

:

hi, !

How do we convert positive numbers into negative ones under one column?

do you have any mixture of positives and negatives in "the range" ?
if so, and you want negatives "as is" and positives into negatives...
pretending "the range" in [A2:A35] -???-

try from vba-editor > immediate window pane
(copy/paste or type) the following and press enter...

[a2:a35] = [a2:a35*-1^(a2:a35>0)]

hth,
hector.