vba - 使用来自不同单元格中的多个工作表的值更新摘要工作表

Question

我对 VBA 很陌生，我试图在下面编写代码，但我遇到了类型不匹配错误(下面突出显示)。我想要达到的目标如下:

我在第一个名为“工作表”的汇总表上的 A 列上有一个属性列表(所有这些都超链接到各自的工作表)

对于每个属性，请转到该属性表

将值 3 单元格右侧复制到具有字符串“此属性的总计”

的单元格中

切换回“工作表”并将值粘贴到 D 列中，在 A 列中相应的属性名称旁边。

我认为问题在于我在另一张表中引用值的方式，但我似乎无法在任何地方找到如何引用相对于具有特定文本的单元格的某个位置的值。

提前谢谢你!

Sub Summary()

    Dim MasterBook As Workbook
    Dim Sht As Worksheet
    Dim Rng, Rng2 As Range

    Set MasterBook = ThisWorkbook
    Set Sht = MasterBook.Worksheets("Sheet")
    Set Rng = Sht.Range("A6:A" & Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row)

    Dim Cell As Range

    For Each Cell In Rng
    Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Cell.Offset(0, 3).Value = Cell.Value("Total for this Property").Offset(0, 3).Value  '<---- This line is giving the error

    Next Cell
End Sub

Answer 1

尝试这个

Sub Summary()

Dim MasterBook As Workbook
Dim Sht As Worksheet
Dim Rng As Range, Rng2 As Range, Rng3 As Range

Set MasterBook = ThisWorkbook
Set Sht = MasterBook.Worksheets("Sheet")
Set Rng = Sht.Range("A6:A" & Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row)

Dim Cell As Range

For Each Cell In Rng
    Set Rng3 = MasterBook.Sheets(Cell.Text).Cells.Find(What:="Total for this Property", LookIn:=xlValues, LookAt:=xlWhole, _
                 MatchCase:=False, SearchFormat:=False)
    If Not Rng3 Is Nothing Then
        Cell.Offset(0, 3).Value = Rng3.Offset(, 3).Value
    Else
        MsgBox "not found"
    End If
Next Cell

End Sub