vba - 使用 VBA 的单元格交叉引用

Question

我已经进行了一些搜索，但无法弄清楚我想要完成的语法。我希望摘要表上的特定静态单元格自动更新为不同工作表上的动态单元格。我从以下代码开始:

Sheets("Summary").Activate
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Current Billing'!R[46]C"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "='Current Billing'!R[45]C[3]+'Current Billing'!R[45]C[4]"

在获得有关同一工作簿的不同主题的一些建议后，我现在知道 .Select 是一个禁忌，应该避免使用。我还知道，当我将代码复制到另一个工作簿以对该项目计费时，我在“当前计费”工作表上的最后一行会发生变化。这导致我修改我的代码，使其更加简单和通用。

我已经想出了如何将单元格值从“当前帐单”表插入到“摘要”表中。但是，如果“当前帐单”表发生变化，“摘要”表将不会自动更新。这是我有这种作品的代码:

Dim ws5 As Worksheet
'ws5 is "Current Billing"
Dim ws6 As Worksheet
'ws6 is "Summary"
Dim LRowB2 As Long
Dim LRowB3 As String
LRowB2 = ws5.Cells(Rows.Count, "B").End(xlUp).Row
LRowB3 = ws5.Cells(LRowB2, "B")
ws6.Cells(1, "B").Formula = LRowB3

我尝试了这两个代码序列，但不断收到错误:

Dim LRowB4 As String
Dim LRowE2 As Long
Dim LRowF2 As Long

LRowB4 = "= & ws5 & ! & LRowB3"
ws6.Cells(2, "B").Formula = "=sum(& LRowE2 &,& LRowF2 &)"

简而言之，有没有办法模仿第一个代码程序的功能，但又具有第二个程序的稳定性和防呆性呢？而且，有没有办法将 =Sum() 公式集成到第二个过程中，这样我就可以在“摘要”页面上的“当前帐单”中添加两个单元格？谢谢您的帮助!

Answer 1

公式的语法类似于 =SUM('Current Billing'!C9, 'Current Billing'!D9)
这将检查错误，并且更加动态

Option Explicit

Public Sub UpdateSummary()
    Dim wsCB As Worksheet, lrCB As Long, cbCellC As Range, cbCellD As Range
    Dim wsSummary As Worksheet, sumCell As Range

    Set wsCB = ThisWorkbook.Worksheets("Current Billing")
    Set wsSummary = ThisWorkbook.Worksheets("Summary")

    lrCB = wsCB.Cells(wsCB.Rows.Count, "C").End(xlUp).Row

    'Verify that we have the same last row for col C and D on sheet "Current Billing"
    If wsCB.Cells(wsCB.Rows.Count, "D").End(xlUp).Row = lrCB Then
        Set cbCellC = wsCB.Cells(lrCB, "C")     '"Current Billing".Cell(lr, C)
        Set cbCellD = wsCB.Cells(lrCB, "D")     '"Current Billing".Cell(lr, D)
        Set sumCell = wsSummary.Cells(2, "B")   '"Summary".Cell(2, B)
        sumCell = "Invalid 'Current Billing' values"    'Default (in case of errors)

        'Check "Current Billing" cells for errors (#N/A, #VALUE!, #REF!, #DIV/0!, etc)
        If Not IsError(cbCellC) And Not IsError(cbCellD) Then

            'Verify that "Current Billing" cells are numbers
            If IsNumeric(cbCellC) And IsNumeric(cbCellD) Then
                Dim cbC As String, cbD As String

                cbC = "'" & wsCB.Name & "'!" & cbCellC.Address(0, 0) 'Current Billing'!C9
                cbD = "'" & wsCB.Name & "'!" & cbCellD.Address(0, 0) 'Current Billing'!D9

                'Final format: =SUM('Current Billing'!C9, 'Current Billing'!D9)

                sumCell.Formula = "=SUM(" & cbC & ", " & cbD & ")"  'Update Summary cell
            End If
        End If
    End If
End Sub

如果公式将包含除数，请验证除数不为 0

但如果您为此使用 VBA，您可以简化语法: sumCell = cbCellC + cbCellD

笔记:

当我们必须在引号内使用引号时，字符串变量会变得更加复杂:

Str with ""double quotes""可以这样 build

str = "Str with """"double quotes""""" , 或

str = "Str with " & Chr(34) & """double quotes""" & Chr(34) , 或

str = "Str with " & Chr(34) & Chr(34) & "double quotes" & Chr(34) & Chr(34)

这个字符串 str = "Str with 'double quotes'"是 Str with 'double quotes'