首页 文章

使用VBA简化复杂的excel公式

提问于
浏览 次
1

由于过度使用LOOKUP公式，我的宏通常很慢 . 我想插入一些VBA变量来加快这些速度 . 我目前正致力于加快以下公式：在Excel中：

=IF(ISNA(MATCH(A2,Summary!B:B,0)),"n",I2-((I2/LOOKUP(2,1/(I:I<>""),I:I))*VLOOKUP(A2,Summary!$G$10:$H$902,2,FALSE)))

在VBA中：

"=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-((RC[-1]/LOOKUP(2,1/(C[-1]<>""""),C[-1]))*VLOOKUP(RC[-9],Summary!R10C7:R902C8,2,FALSE)))"

我需要替换的部分是 LOOKUP(2,1/(C[-1]<>""""),C[-1]) . 所有这一切都是引用列I中的最后一个非空单元格 . 现在我有以下代码返回VBA中最后一个单元格的地址

Sub FormulaTest()
Set lRow = Range("I1").SpecialCells(xlCellTypeLastCell).Address

End Sub

我试图弄清楚如何将这个“lRow”实现到公式的VBA代码中 . 任何人都可以引导我朝着正确的方向前进吗？

**编辑1请参阅下面的费尔南多的评论 . 他有正确的想法，但解决方案仍然有点偏 . 我试着在几条评论中更好地解释它：首先，第一行始终是 Headers 行，最后一行始终是总和行，当前选项卡是“销售”选项卡，以及任何给定的行数销售选项卡会有所不同（可能是I1：I59，可能是I：1：I323） .

在这个例子中，I1是行 Headers ，I59是I2：I58的总和 . 行I2：I58是美元金额 . 我的宏将这个公式放在J2：J58中 . 此公式将每行的美元金额（I2：I58）作为总计（I59）的百分比，并将其乘以“摘要”选项卡（VLOOKUP）上的输入金额 . 然后从列I中的美元值中按比例减去该量，其中J单元显示结果 .

我希望在上面的公式中消除对LOOKUP函数（选择最后一个非空单元格）的需要：LOOKUP（2,1 /（C [-1] <>“”“”），C [-1]） .

**编辑2费尔南多的解决方案奏效了 . 谢谢大家的意见

excel vba excel-vba excel-formula

2 回答

  • 0

    这将返回第I列中的最后一个非空行

    with Worksheets("Summary")
lRow = .Cells(.Rows.Count, "I").End(xlUp).Row
end with

    所以你的代码就是

    sub testy
dim lRow as long
with Worksheets("Summary")
lRow = .Cells(.Rows.Count, "I").End(xlUp).Row
end with

"=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-_
((RC[-1]/R"&lRow&"C[-1])*VLOOKUP(RC[-9],Summary!R10C7:R902C8,2,FALSE)))"

    在您的解决方案中，您正在使用 xlCellTypeLastCell . 这非常有用，但它根据 UsedRange 计算，这可能不是您想要的 . 有了这个，如果你有数据行 n ，然后你更新数据，现在你有更少的记录， xlCellTypeLastCell 的最后一行仍然是 n ，所以要小心 .

    回复于
  • 1

    假设您正在活动工作表上完成所有工作，请查看“摘要”工作表：

    Sub fillCol()

    Dim aRow As Long, bRow As Long

    aRow = Cells(Rows.Count, "I").End(xlUp).Row
    bRow = Sheets("Summary").Cells(Rows.Count, "I").End(xlUp).Row

    Range("J2:J" & aRow).FormulaR1C1 = "=IF(ISNA(MATCH(RC[-9],Summary!C[-8],0)),""n"",RC[-1]-" _
                       & "((RC[-1]/" & aRow & ")*VLOOKUP(RC[-9],Summary!R10C7:R" & bRow & "C8,2,FALSE)))"

End Sub

    您需要更改包含连续范围的列（以确定最后一行）

    回复于

相关问题