none
Copy a Value Instead of a Formula RRS feed

  • Question

  • Range("G3:G92") has a VLOOKUP formula in each cell. When I run the code line:
        Range("G3:G92").Copy Destination:=Range("M3")
    it copies the formulae and gives me unwanted results in column M.

    How do I change that line of code so that it copies the cell value, not the cell contents (formulae)?
    Sunday, June 12, 2016 2:26 AM

Answers

  • Many thanks OssieMac; I was starting to wonder whether I was using the wrong tool for the job.

    I've got 15 lines of code currently doing the job on 2 ranges and I was trying to streamline the code. Using your kindly supplied code, that's reduced it to 6 lines.

    Perfect result!

    • Marked as answer by DaviDWF2 Sunday, June 12, 2016 4:07 AM
    Sunday, June 12, 2016 4:07 AM

All replies

  • The example you have posted does not work with Paste Values. You can Copy with one line and Paste Values on the next line. You can record code to get correct syntax.

    The following also works. It does not leave the pasted area selected like with Paste Values.

    Range("M3").Resize(Range("G3:G92").Rows.Count, Range("G3:G92").Columns.Count) = Range("G3:G92").Value

    However, I personally prefer to assign the range to a range variable like the following. The Resize code is simpler to code.

        Dim rng As Range
       
        Set rng = Range("G3:G92")
       
        Range("M3").Resize(rng.Rows.Count, rng.Columns.Count) = rng.Value


    Regards, OssieMac

    Sunday, June 12, 2016 3:36 AM
  • Many thanks OssieMac; I was starting to wonder whether I was using the wrong tool for the job.

    I've got 15 lines of code currently doing the job on 2 ranges and I was trying to streamline the code. Using your kindly supplied code, that's reduced it to 6 lines.

    Perfect result!

    • Marked as answer by DaviDWF2 Sunday, June 12, 2016 4:07 AM
    Sunday, June 12, 2016 4:07 AM