Most efficient code to "flip" a range of values + vs -

[EagleOne]

2003/2007



Range("A1").Value = Range("A1").Value * -1

will quickly flip "A1" 9999 to -9999

Let's assume that
Set myRange = Range("A1:A3")

How does one "flip" the values in myRange?

As myRange.Value = myRange.Value * -1 does not work.

TIA EagleOne

 

[Guest]

If you have to resort to code use this:-

Sub marine()
Set myrange = Range("A1:A3")
For Each c In myrange
c.Value = c.Value * -1
Next
End Sub

But if code can be avoided put -1 in a cell and copy. Select the range to
filp and paste special|multiply.

Mike

 

[Incidental]

Hi there

you could try something like the code below to iterate through each
cell in the range

Option Explicit
Dim MyCell, MyRng As Range

Private Sub CommandButton1_Click()
Set MyRng = [A1:A3]
For Each MyCell In MyRng
MyCell.Value = MyCell.Value * -1
Next MyCell
End Sub

Hope this helps

S

 

[EagleOne]

Mike, I can not use the -1 in the CopyTo Cells PasteSpecial/Multiply option.

The For Next works fine. Thanks

Any thoughts about initializng, multipling, pasting an array?




Mike H <M

 

[Peter T]

Look at PasteSpecial, values & multiply
Start by copying a cell that contains -1
Record a macro

Regards,
Peter T

 

[Dana DeLouis]

How does one "flip" the values in myRange?

As myRange.Value = myRange.Value * -1
does not work.

Hi. To do it without a loop requires a slightly different technique:

Sub Demo()
[A1:A10] = [- A1:A10]
End Sub

 

[EagleOne]

Dana,

is [A1:A10] = [- A1:A10] an Array usage in VBA?

EagleOne

How does one "flip" the values in myRange?
As myRange.Value = myRange.Value * -1
does not work.

Hi. To do it without a loop requires a slightly different technique:

Sub Demo()
[A1:A10] = [- A1:A10]
End Sub

 

[EagleOne]

Dana,

Can one get a variable into the [ ]?

I attempted:
[myRange] = [- myRange]

then [myRange.address] = [- myRange.address]

then x = myRange.address
[x] = [- x]

to no avail.

How does one "flip" the values in myRange?
As myRange.Value = myRange.Value * -1
does not work.

Hi. To do it without a loop requires a slightly different technique:

Sub Demo()
[A1:A10] = [- A1:A10]
End Sub

 

[Roger Govier]

Hi

Sub test()
[myrange] = [-myrange]
End Sub

worked fine for me in XL2003 and XL2007

--
Regards

Roger Govier

Dana,

Can one get a variable into the [ ]?

I attempted:
[myRange] = [- myRange]

then [myRange.address] = [- myRange.address]

then x = myRange.address
[x] = [- x]

to no avail.

How does one "flip" the values in myRange?
As myRange.Value = myRange.Value * -1
does not work.

Hi. To do it without a loop requires a slightly different technique:

Sub Demo()
[A1:A10] = [- A1:A10]
End Sub