VBA中的匹配函数给出了编译错误 [英] Match function in VBA gives compile error

问题描述

我有一本带有两个工作表的工作簿."2020-2021"工作表的D列中有一些唯一的编号.匹配结果应显示在欠款"工作表中.欠款"中的C2单元格有一个数字，我想在2020-2021年的D列中进行匹配.输入以下代码.

I have a work book with two Worksheets. '2020-2021' Worksheet has some unique numbers in its Column D. Result of Match should come to 'Arrears' Worksheet. C2 cell in 'Arrears' has a number which I want to Match in Column D in 2020-2021. Entered the following code.

Range("C3").Value = WorksheetFunction.Match(Range("c2").value,Range('2020-2021'!d11:d206),0)

给出编译错误

推荐答案

限定(使用变量)

• 如果您想学习一些东西，请学习前两个版本.
• 否则，请选择第三版中的一种解决方案.

合格；那甚至是什么意思?

• 与这种情况有关，例如 Range("C3")不合格.我们不确定它指的是(属于).它是 ActiveSheet.Range("C3")的缩写，这意味着如果我们选择 Sheet1 ，它将引用上的 C3 Sheet1 ，如果我们选择 Sheet2 ，它将引用 Sheet2 等上的 C3 .

• Related to this case, e.g. Range("C3") is not qualified. We don't know for sure where it refers to (belongs). It is short for ActiveSheet.Range("C3") which means that if we select Sheet1 it will refer to C3 on Sheet1, if we select Sheet2 it will refer to C3 on Sheet2 etc.

但是，如果我们使用 Worksheets("Sheet1").Range("C3")，我们就限定了范围，并且选择了哪个工作表，它将始终引用<工作表 Sheet1 上的code> C3 (不是100％正确，因为...).

But if we use Worksheets("Sheet1").Range("C3") we have qualified the range and which ever worksheet we select, it will always refer to C3 on worksheet Sheet1 (Not 100% true because...).

类似于范围情况，我们说 Worksheets("Sheet1").Range("C3")中的工作表不合格.该表达式实际上是 ActiveWorkbook.Worksheets("Sheet1").Range("C3")的缩写.

Similarly to the range case, we are saying that the worksheet in Worksheets("Sheet1").Range("C3") is not qualified. The expression is actually short for ActiveWorkbook.Worksheets("Sheet1").Range("C3").

要使其合格，我们可以做 Workbooks("Test.xlsm").Worksheets("Sheet1").Range("C3").现在，我们说范围是完全限定的，工作表也是如此.这似乎常常不切实际，因此 ThisWorkbook 可以拯救".

To qualify it, we can do Workbooks("Test.xlsm").Worksheets("Sheet1").Range("C3"). Now we are saying that the range is fully qualified, as is the worksheet. This might often seem unpractical, so ThisWorkbook 'comes to the rescue'.

如果您需要引用包含此代码的工作簿，只需使用 ThisWorkbook .您不必在乎它是否被称为 Test.xlsm 或 Last Years官方库存报告what.xlsm ......，只需使用 ThisWorkbook .

If you need to refer to the workbook containing this code, just use ThisWorkbook. You don't care if it is called Test.xlsm or Last Years Official Inventory Report whatever.xlsm... just use ThisWorkbook.

关于我们的案例，我们可以使用以下方法完全限定范围:

Related to our case, we can then fully qualify our range using:

ThisWorkbook.Worksheets("Arrears").Range("C3")

Application.Match与WorksheetFunction.Match

Sub qualifyAP()
    
    ' Define workbook.
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    ' Define worksheets.
    Dim src As Worksheet
    Set src = wb.Worksheets("2020-2021")
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("Arrears")
    
    ' Use the Application version of Match.
    Dim Result As Variant ' can hold any datatype incl. error value.
    Result = Application.Match(tgt.Range("C2").Value, src.Range("D11:D206"), 0)
    If Not IsError(Result) Then
        tgt.Range("C3").Value = Result
        'MsgBox "Data found and transferred."
    Else
        MsgBox "Data not found. Nothing done."
    End If

End Sub

Sub qualifyWF()
    
    ' Define workbook.
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    ' Define worksheets.
    Dim src As Worksheet
    Set src = wb.Worksheets("2020-2021")
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("Arrears")
    
    ' Use the WorksheetFunction version of Match.
    On Error Resume Next
    tgt.Range("C3").Value = WorksheetFunction.Match(tgt.Range("C2").Value, _
                                                    src.Range("D11:D206"), _
                                                    0)
    If Err.Number = 0 Then
        'MsgBox "Data found and transferred."
    Else
        MsgBox "Data not found. Nothing done."
    End If
    On Error GoTo 0

End Sub

Sub qualifyQF()
    
    Worksheets("Arrears").Range("C3").Value = WorksheetFunction _
      .Match(Worksheets("Arrears").Range("C2").Value, _
             Worksheets("2020-2021").Range("D11:D206"), _
             0)
' or:
    With Worksheets("Arrears")
        .Range("C3").Value = WorksheetFunction _
          .Match(.Range("C2").Value, _
                 Worksheets("2020-2021").Range("D11:D206"), _
                 0)
    End With
' or even:
    With Worksheets("Arrears").Range("C3")
        .Value = WorksheetFunction _
          .Match(.Offset(-1).Value, _
                 Worksheets("2020-2021").Range("D11:D206"), _
                 0)
    End With

End Sub

这篇关于VBA中的匹配函数给出了编译错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！