I want to take values from column J, multiply them by -1 and put them in column I.
I have this line:
Range("I2:I9").Value = "=J2*-1"
which puts the formula in column I and calculates the value, but I simply want the value.
Alternatively if the values in column J can be multiplied by -1 in place, that would work too.
What can I do?
1
Try this – just tested and it works for me:
Sub InvertNum()
Dim ws As Worksheet
Dim rngSource As Range, rngTarget As Range
Set ws = Application.ActiveSheet
Set rngSource = ws.Range("J2:J9")
Set rngTarget = ws.Range("I2:I9")
rngTarget.Value = ws.Evaluate("-1 * " & rngSource.Address)
End Sub
4
This code inverts signals in place:
Sub invertSignal()
Dim rng As Range ' Declares rng as Range
Set rng = Application.ActiveSheet.Range("J2:J9") ' sets rng to desired range
' Loops for each cell in range, multiplying values by -1
For Each cell In rng
cell.Value = cell.Value * -1
Next
End Sub
Another way to do it in place. You need a single empty cell, let it be M1:
Sub Inversion()
With Range("M1")
.Value = -1
.Copy
Range("J2:J9").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
.ClearContents
End With
End Sub