Copy paste the values of cells from Excel

Question

I want to copy paste the cell values what we see post pressing F9 in a cell into word. Is there any short and quick way to do this?

For example - if I have a formula: =AVERAGE(E12/H12,E11/H11,E10/H10)

if I press F9 6 times: =AVERAGE(18.8064516129032/18.0469173899242,15.5862068965517/13.1790029444614,16.6470588235294/16.6666666666667)

I want to paste the above to Word document instead of the formulae with cell references. I want to do these for a large number of cells. Doing F9 and then pasting each cell is time consuming.

Is there any shortcut/macros or any quick way to do the above without pressing F9 six times? My formulae contains absolute references, relative references and array formulae as well in some instances. So, looking for something that would work for a range of cells where I would like to replicate the F9 feature.

Answer 1

You can use this macro code.

Sub FormulaPrec()
    Dim fText As String, prec
    fText = Selection.Formula
    For Each prec In Selection.DirectPrecedents
       fText = Replace(fText, prec.Address(0, 0), Str(prec.Value))
    Next prec
    MsgBox fText
    ' copy to clipboard
    CreateObject("htmlfile").parentWindow.clipboardData.setData "text", fText
End Sub

If different types of addresses (with and without $) are used in a formula you should convert all addresses to the same type before changing them to values.

Sub FormulaPrec1()
    Dim fText As String, prec
    fText = Selection.Formula
    fText = Application.ConvertFormula(fText, xlA1, xlA1, xlAbsolute)
    For Each prec In Selection.DirectPrecedents
       fText = Replace(fText, prec.Address, Str(prec.Value))
    Next prec
    MsgBox fText
    ' copy to clipboard
    CreateObject("htmlfile").parentWindow.clipboardData.setData "text", fText
End Sub

Before you use it, you need to select the cell that contains the formula. Using the Str function in this code is unnecessary if you use a period as a decimal separator in your Excel. In the last line, the formula is copied to the clipboard, from where it can be pasted with Ctrl+V to the desired location, e.g. in a Word document.

Answer 2

You can use

="AVERAGE("&E12&"/"&H12&","&E11&"/"&H11&","&E10&"/"&H10&")"

Then copy the result of this formula.