Multiply a range by -1
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 |
Multiply a range by -1
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 -- Rick (MVP - Excel) "Greg Snidow" wrote in message ... 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 |
Multiply a range by -1
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 "Greg Snidow" wrote in message ... 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 |
Multiply a range by -1
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 -- Gary''s Student - gsnu200907 "Greg Snidow" wrote: 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 |
Multiply a range by -1
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? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200907 "Greg Snidow" wrote: 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 |
All times are GMT +1. The time now is 12:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com