Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Copy & Paste Special Mutliply (Excel 2010)

    Hello,

    I'm trying to write a bit of code that would simply multiply a range by -1 WITHOUT having to actually put the "-1" in the Excel worksheet in cell AA1. How can I just define the "-1" as a value or integer in the code, then "copy" the "-1" so the last two lines of code will work (in other words so the copy pastespecial multiply will work).

    Thanks so much!
    Lana

    Range("AA1") = "-1"

    Range("AA1").Select
    Selection.Copy

    Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,944
    Thanks
    0
    Thanked 203 Times in 184 Posts
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. The Following User Says Thank You to macropod For This Useful Post:

    LJM (2014-05-07)

  4. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,534
    Thanks
    3
    Thanked 147 Times in 140 Posts
    Try this
    Code:
    Dim rng As Range
    Set rng = Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    rng = Evaluate("=" & rng.Address & "*-1")
    Are you sure the range being selected by .End(xlUp) is what you expected? A shorter way would simply define it as Range("E1:E2")
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    LJM (2014-05-07)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •