Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiply range values with Lookup-value? | Excel Programming | |||
How do I identify a number in a range and then multiply? | Excel Programming | |||
Multiply a range | Excel Programming | |||
Multiply a Range by a Constant | Excel Programming | |||
Multiply range | Excel Programming |