Copy formula result as a true value

**junada0** · 01-22-2010, 02:27 PM

Is there a way to copy a formula result as the actual value and not the formula,

I can do copy and paste using "value" option but would like an automated formula or code option if possible.

In the example, D4 copies C4, would like D4 to show "AB" and not the formula "=C4"

Thanks

**protonLeah** · 01-22-2010, 05:28 PM

In Excel Options, Advanced, Display option for this worksheet verify that the box for Show formulas.... is unchecked.

**junada0** · 01-22-2010, 06:06 PM

Not really what i am looking for, I would like to see the true value "AB" in D4 cell only (or any other cell) and not the formula

**protonLeah** · 01-22-2010, 06:44 PM

Sorry, the picture you posted indicates that you have "show formulas" selected. I was only trying to get you to verify that 'Show formulas" option is unchecked.

**junada0** · 01-22-2010, 08:25 PM

No Problem,

Understand your logic, I had used the apostrophe before the = sign to allow formula to be shown in the diagram, sorry for any confusion. '=CONCATENATE(A4,B4).

**martindwilson** · 01-23-2010, 10:29 AM

simple code is

Code:

Sub fixvalue()
    For Each c In Worksheets("Sheet1").Range("d1:D10").Cells
         c.Value = c.Offset(0, -1).Value
    Next
End Sub

**Andy Pope** · 01-23-2010, 11:02 AM

No need to loop

Code:

    With Worksheets("Sheet2").Range("d1:D10")
        .Value = .Offset(, -1).Value
    End With

For a single cell you can enter =C4 in the cell D4 and press F9 to convert formula to value.

**gruf1968** · 01-24-2010, 12:38 PM

Can I just clarify. Would this type of formulae allow me to to take a "snapshot" of a stock level at the end of each month and retain it in excel 2007 as an actual value?