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