copy cell formula

[redspanna]

Hi

Cell B41 contains the formula

=$B384

anybody know how I can copy this formula value preceeded by a " ' "
and paste into cell AL1

so with above example AL1 will now be populated with following text
'=$B384

note!!!

Cell B41 will hold different values and will NOT always be =$B384

thanks in advance

 

[Gerald Higgins]

Are you hinting that you want to do something different if B41 does NOT contain =$B384 ?

 

[redspanna]

Hi

Whatever the formula in cell B41 is I just want to copy it preceeded with a " ' " and paste it into another cell

as I said the cell formula in B41 could be anything

hope you can help

 

[Trouttrap2]

this is tricky…..it uses an old artifact of Excel Macro.

Highlight cell AL1 then CTRL+F3 to Define a Named Range
Name the range FindCell
In the 'refers to' type in =get.cell(6,B41), click ADD, Click OK
then in Cell AL1, enter =FindCell
This returns the formula value in cell B41 as text so you don't need to preceed it with a ' mark.

 

[shg]

I use a UDF for this, primarly for posting examples in the forum:

Function GetFormula(r As Range, _
                    Optional ByVal sText As String = "", _
                    Optional ByRef bA1 As Boolean = True) As String
    ' shg 2007
 
    Dim sL          As String   ' left brace for array formula
    Dim sR          As String   ' right brace for array formula
    Dim sAdrs       As String
 
    Select Case FormulaType(r)
        Case 0              ' no formula
            sAdrs = r.Address(False, False)
            If Len(sText) Then sText = " " & sText
            sText = sText & ": "
            GetFormula = r.Address(False, False) & sText & "Input"
            Exit Function
        Case 1
            sAdrs = r.Address(False, False)
        Case 2, 3
            sL = "{"
            sR = "}"
            sAdrs = r.Address(False, False)
    End Select
 
    ' pad text with a leading space and trailing colon
    If Len(sText) Then sText = " " & sText
    sText = sText & ": "
    GetFormula = sAdrs & sText & sL & IIf(bA1, r(1).Formula, r(1).FormulaR1C1) & sR
End Function
 
Function FormulaType(cell As Range) As Long
    ' shg 2007
 
    ' Returns the type of formula in cell
    '   multi-cell array formula .... 3
    '   single-cell array formula ... 2
    '   regular formula ............. 1
    '   no formula .................. 0
 
    With cell
        If .HasArray Then
            FormulaType = IIf(.CurrentArray.Cells.Count > 1, 3, 2)
        Else
            FormulaType = IIf(.HasFormula, 1, 0)
        End If
    End With
End Function

So for your example,

=GetFormula(B41) returns B41: =$B384

 

[Armando Montes]

You might try this UDF:

Function DisplayFormula(cel As Range)
        DisplayFormula = cel.Formula & ""
End Function