Multiply a range by -1

[Greg Snidow]

Greetings everyone. I need to take all the values in a range, and make them
negative. I tried....

Range("E" & LastRow - 3 & ":I" & LastRow - 3) = _
Range("E" & LastRow - 3 & ":I" & LastRow - 3) * -1

I know I can put a -1 in a cell, then copy and paste special - multiply, but
I was looking for something the user will not see. Any ideas? Thank you.

Greg

 

[Rick Rothstein]

Why not just loop through the cells...

Dim C As Range
......
......
For Each C In Range("E" & LastRow - 3 & ":I" & LastRow - 3)
C.Value = -C.Value
Next

 

[Peter T]

Normally user will not see your pasteSpecial multiply, ensure the copy cell
is initial free then clear it when done. "Make them negative" is slightly
ambiguous, couple of non pasteSpecial ideas

Sub SampleData()
'values -100 to +99 in colA with copy in col-B
With Range("A1:A1000")
.Formula = "=INT(RAND()*200)-100"
.Value = .Value
Range("A1:A1000").Value = .Value
End With
End Sub


Sub Negit()

' col-A to Col-B as all -ve
arr = Range("A1:A1000").Value
For i = 1 To UBound(arr)
arr(i, 1) = -Abs(arr(i, 1))
Next
Range("B1:B1000").Value = arr


' col-A to col-C negated, ie value x (-1)
arr = Range("A1:A1000").Value
For i = 1 To UBound(arr)
arr(i, 1) = -(arr(i, 1))
Next
Range("C1:C1000").Value = arr

End Sub

Regards,
Peter T

 

[Gary''s Student]

Sub Inverter()
Dim r As Range, LastRow As Long, rr As Range
LastRow = 9
Set r = Range("E" & LastRow - 3 & ":I" & LastRow - 3)
For Each rr In r
rr.Value = -rr.Value
Next
End Sub

 

[Greg Snidow]

Thanks for the help. So who is Gary? Anyhow, it works like a charm. OK, I
get that r is a range of cells, and that the loop goes through the range, but
how does Excel know how to treat rr? We are not telling it that rr is a
cell, or cell value, so how does it know what to do? I'm having a hard time
understanding the concept of what is going on. Usually, my code is riddled
with MsgBox(Variable), so I can "see" what is going on, but MsgBox(r) does
not work. MsgBox(rr) does work, so I can see that the sub is treating rr as
the cell value, but how did it know what to do?