Excel VBA 编程处理公式

Question

Private Sub RefreshBenchmarks_Click()
Dim ws As Worksheet
Set ws = Worksheets("INDEX CHANGES")
If Not IsError(Application.Offset(ws!S3, _
                           Application.Match(Worksheets("ASX200").B8, _
                           ws.Range("CONSTITUENT_CHANGES"), 0), 0, 1, 1) = "D" _
                           And _
                           Application.Offset(ws!N3, _
                           Application.Match(Worksheets("ASX200").B8, _
                           Range("CONSTITUENT_CHANGES"), 0), 0, 1, 1) = "Y") Then
    Worksheets("ASX200").J8 = 0

我是vba编程的新手，有人可以解释一下这里的错误是什么。错误指出对象不支持此属性或方法。它停止在 If 语句处。任何帮助表示赞赏。

Answer 1

未经测试，但类似这样的东西...

Private Sub RefreshBenchmarks_Click()
    Dim wsIdx As Worksheet, wsAsx As Worksheet, v, m
    Set wsIdx = Worksheets("INDEX CHANGES")
    Set wsAsx = Worksheets("ASX200")
    m = Application.Match(wsAsx.Range("B8").Value, _
                          wsIdx.Range("CONSTITUENT_CHANGES"), 0)
    If Not IsError(m) Then
        If wsIdx.Range("S3").Offset(m, 0).Value = "D" And _
           wsIdx.Range("N3").Offset(m, 0).Value = "Y" Then
            wsAsx.Range("J8").Value = 0
        End If

    End If
End Sub