将公式的一部分从相对引用更改为绝对引用

Question

我正在努力解决单元格引用问题。我在单元格B2中有以下公式：

=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))

接下来，我使用自动填充函数来确保最后一行之前的所有单元格都具有此公式。在 C 列中，我有"领先"的静态/硬值。我需要从大到小对 C 列中的值进行排序。如果我这样做，公式会在电子表格中移动。例如，单元格 B2 中的新公式为：

=INDEX(Sheet2!C:C,MATCH(A210,Sheet2!A:A,0))

因此，理想情况下，我想确保MATCH(A2)转换为MATCH($A$2)并且第 3 行中的公式为：

=INDEX(Sheet2!C:C,MATCH(A3,Sheet2!A:A,0))

但是，我使用以下VBA模块来创建公式：

Range("B2").Select
With Range("B2")
    .Formula = "=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))"
    .AutoFill Destination:=Range("B2:B" & LastRow)
End With

这使得从一开始就无法使用锁定的$A$2。

我知道以下功能：

Application.ConvertFormula(Formula:=oRange.Formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)

但是，我正在努力如何将此功能插入我的宏。如果我使用以下宏：

Range("M2").Select
With Range("M2")
    .Formula = "=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))"
    .AutoFill Destination:=Range("M2:M" & LastRow)
End With                                                         
Range("M2:M" & LastRow).Formula = Application.ConvertFormula(Formula:="=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0))", fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)

我在每一行中得到以下公式：

 =INDEX(Sheet2!$C:$C,MATCH($A$2,Sheet2!$A:$A,0))

我感谢您的帮助！

Answer 1

据我了解，您的问题是锁定 MATCH 函数的第一个参数并在填充时同时更新它，对吗？如果是这样，您可以填写公式的解锁版本，然后使用替换添加美元符号。见下文：

Dim rng As Range
Dim cl As Range
Set rng = "define your range here"
For Each cl In rng
    cl.Formula = Replace(cl.Formula, "MATCH(A" & cl.Row, "MATCH($A$" & cl.Row)
Next cl 

这是你要找的吗？

Answer 2

尝试将公式中的单元格分配给变量。将变量引入公式时，请使用 .address 或 .addresslocal 属性来冻结或保留相对引用。

Dim MyMatch as range
Set MyMatch = range("a2")
range("m2").formula = “INDEX(Sheet2!C:C,MATCH(” & MyMatch.Address & “,Sheet2!A:A,0))”

Answer 3

我要感谢你们两位的回答。您的两种方法都有效，但是由于行数很大，宏需要很长时间才能运行。最后，我缩短了这个时间，并使用以下宏实现了我想要的结果：

Range("A1").Select
ActiveWindow.DisplayFormulas = True
Cells.Select
Selection.Replace What:="Sheet1!B", Replacement:="Sheet1!$B", LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
ActiveWindow.DisplayFormulas = False

不过，谢谢你的时间。