显示单元格的公式，但用值代替引用 [英] Show formula of a cell, but values instead of references

问题描述

我正在使用一个公式来显示另一个单元格中的一个单元格公式.

I am using a formula to show a cells formula in another cell.

我想在此公式中显示每个引用的值，而不是引用.

I want to show the values of each reference in this formula, instead of the reference.

例如:

Ex:

= $ R $ 16 + R19 *($ T $ 15- $ R $ 16)

=$R$16+R19*($T$15-$R$16)

想成为

Want it to be

= 3 + 2 *(4-2)

=3+2*(4-2)

我现在使用的函数按原样显示公式

Function I am using now to show formula as it is

Function GetFormula(Cell As Range) As String
   GetFormula = Cell.Formula
End Function

推荐答案

这是一个非常基本的示例，可以处理类似的情况

Here is a very basic example which can handle cases like

=B1+C1-D1/E1
=$B$1+C1*($B$1-$D$1)
=B1+C1-(D1/E1)
=B1+C1-(D1/E1)
=$B$1*C1*($B$1/$D$1)

假设:

1. 假设上述公式位于A1至A5单元格中
2. 所有单元格都在同一张纸上
3. 没有工作表功能，例如SUM，Vlookup等
4. 没有错误处理完成.假设您会照顾好它

示例工作表

代码:

Sub Sample()
    Dim i As Long
    For i = 1 To 5
        Debug.Print ConvertToValues(Range("A" & i))
    Next i
End Sub

Function ConvertToValues(rng As Range)
    Dim sTmp As String, sOpr As String, sOrig As String
    Dim s As String, v As String
    Dim MyAr
    Dim i As Long

    sOpr = "+,/,-,*,&,(,),{,},[,]"

    MyAr = Split(sOpr, ",")

    sTmp = Replace(rng.Formula, "$", "")
    sOrig = sTmp

    For i = LBound(MyAr) To UBound(MyAr)
        sTmp = Replace(sTmp, MyAr(i), "SIDROUT")
    Next i

    MyAr = Split(sTmp, "SIDROUT")

    For i = LBound(MyAr) To UBound(MyAr)
        s = MyAr(i)

        If Len(Trim(s)) <> 0 Then
            v = Range(s).Value
            sOrig = Replace(sOrig, s, v)
        End If
    Next i

    If sOrig <> "" Then _
    ConvertToValues = "=" & sOrig
End Function

输出

注意:

让我知道以上代码在特定情况下是否失败，我将更新代码.

Let me know if the above code fails in a particular scenario and I will update the code.

这篇关于显示单元格的公式，但用值代替引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！