MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can someone PLEASE help me fix this macro?


Posted by Tim Johnson on December 27, 2001 2:15 PM

Can someone PLEASE help me fix this macro? I need to be able to make multiple numbers negative. I know you can do it by copying a negative 1, and then paste- special-multiply to make it work. I have created the following macro that works for one cell at a time, but I need to be able to do many cells at the same time, and some of them will not be adjacent. Any ideas??
THANK YOU SO MUCH IN ADVANCE!!!!!!!


Sub NEGATIVE1()
'
' NEGATIVE1 Macro
' Macro recorded 12/27/01 by Tim Johnson
'

'
ActiveCell.Offset(0, 43).Range("A1").Select
ActiveCell.FormulaR1C1 = "-1"
ActiveCell.Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-2
ActiveCell.Offset(0, -43).Range("A1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _
False, Transpose:=False
ActiveCell.Offset(0, 43).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -43).Range("A1").Select

End Sub


Posted by Mike on December 27, 2001 2:36 PM

Well, you could do this for each cell:

If Worksheets("Sheet1").Range("A1").Value > 0 Then _
Worksheets("Sheet1").Range("A1").Value = _
Worksheets("Sheet1").Range("A1").Value * -1

-Mike

Sub NEGATIVE1() ' ' NEGATIVE1 Macro ' Macro recorded 12/27/01 by Tim Johnson ' ActiveCell.Offset(0, 43).Range("A1").Select ActiveCell.FormulaR1C1 = "-1" ActiveCell.Select Selection.Copy ActiveWindow.LargeScroll ToRight:=-2 ActiveCell.Offset(0, -43).Range("A1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, 43).Range("A1").Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, -43).Range("A1").Select

Posted by Iachimo on December 27, 2001 4:41 PM

Sub NEGATIVE1() ' ' NEGATIVE1 Macro ' Macro recorded 12/27/01 by Tim Johnson ' ActiveCell.Offset(0, 43).Range("A1").Select ActiveCell.FormulaR1C1 = "-1" ActiveCell.Select Selection.Copy ActiveWindow.LargeScroll ToRight:=-2 ActiveCell.Offset(0, -43).Range("A1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, 43).Range("A1").Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, -43).Range("A1").Select

Try this :-

Sub Convert_To_Negatives()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell) And cell.Value > 0 Then cell.Value = cell.Value * -1
Next
End Sub

The above will, however, convert cells containing formulas to values only.
If you want to exclude formuls cells from the conversion :-

Sub Convert_To_Negatives()
Dim cell As Range
For Each cell In Selection
If IsNumeric(cell) And cell.Value > 0 And Left(cell.Formula, 1) <> "=" Then cell.Value = cell.Value * -1
Next
End Sub


Posted by More Beer!!! on December 28, 2001 6:04 AM

THEIR'S WON'T WORK, BUT THIS ONE WILL!!!!

Sub NEGATIVE2()
'
' NEGATIVE2 Macro
' Macro recorded 12/28/01 by More Beer!!!
'

'

Dim CellContents As String

For Each Cell In Selection

CellContents = Right(Cell.FormulaR1C1, Len(Cell.FormulaR1C1) - 1)
Cell.FormulaR1C1 = "=(" & CellContents & ")*-1 "
CellContents = ""

Next Cell


End Sub

Can someone PLEASE help me fix this macro? I need to be able to make multiple numbers negative. I know you can do it by copying a negative 1, and then paste- special-multiply to make it work. I have created the following macro that works for one cell at a time, but I need to be able to do many cells at the same time, and some of them will not be adjacent. Any ideas??

Sub NEGATIVE1() ' ' NEGATIVE1 Macro ' Macro recorded 12/27/01 by Tim Johnson ' ActiveCell.Offset(0, 43).Range("A1").Select ActiveCell.FormulaR1C1 = "-1" ActiveCell.Select Selection.Copy ActiveWindow.LargeScroll ToRight:=-2 ActiveCell.Offset(0, -43).Range("A1").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(0, 43).Range("A1").Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(0, -43).Range("A1").Select

Posted by Tim Johnson on December 28, 2001 6:44 AM

That only works on formulas!!!! HELP PLEASE!!!!

That only works on formulas!!!! If the cell has only a value in it, your macro cuts off the first number in the cell!! Can someone help me fix that part of it???

' ' NEGATIVE2 Macro ' Macro recorded 12/28/01 by More Beer!!! ' Dim CellContents As String For Each Cell In Selection Cell.FormulaR1C1 = "=(" & CellContents & ")*-1 " CellContents = "" End Sub

: Can someone PLEASE help me fix this macro? I need to be able to make multiple numbers negative. I know you can do it by copying a negative 1, and then paste- special-multiply to make it work. I have created the following macro that works for one cell at a time, but I need to be able to do many cells at the same time, and some of them will not be adjacent. Any ideas?? : THANK YOU SO MUCH IN ADVANCE!!!!!!! : : Sub NEGATIVE1() : ' : ' NEGATIVE1 Macro : ' Macro recorded 12/27/01 by Tim Johnson : ' : ' : ActiveCell.Offset(0, 43).Range("A1").Select : ActiveCell.FormulaR1C1 = "-1" : ActiveCell.Select : Selection.Copy : ActiveWindow.LargeScroll ToRight:=-2 : ActiveCell.Offset(0, -43).Range("A1").Select : Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _ : False, Transpose:=False : ActiveCell.Offset(0, 43).Range("A1").Select : Application.CutCopyMode = False : Selection.ClearContents : ActiveCell.Offset(0, -43).Range("A1").Select : End Sub


Posted by Ivan F Moala on December 28, 2001 12:06 PM

Re: That only works on formulas!!!! HELP PLEASE!!!!

What do you want it to work on ?
Formulas and constants ?
or Just constatants

Ivan That only works on formulas!!!! If the cell has only a value in it, your macro cuts off the first number in the cell!! Can someone help me fix that part of it???


Posted by Iachimo on December 28, 2001 3:32 PM

Despite claims to the contrary, this one works

Try this :- Sub Convert_To_Negatives() Dim cell As Range For Each cell In Selection If IsNumeric(cell) And cell.Value > 0 Then cell.Value = cell.Value * -1 Next End Sub The above will, however, convert cells containing formulas to values only. If you want to exclude formuls cells from the conversion :- Sub Convert_To_Negatives() Dim cell As Range For Each cell In Selection If IsNumeric(cell) And cell.Value > 0 And Left(cell.Formula, 1) <> "=" Then cell.Value = cell.Value * -1 Next End Sub


Posted by Paulina on December 28, 2001 3:45 PM

Also it converts positives to negatives, which was not requested.


Posted by Imogen on December 28, 2001 3:47 PM

Seems like someone has had too much beer.