vba - 运行时 13 类型不匹配

Question

执行以下代码时出现运行时错误 13

Dim sh, shmem As Worksheet
Dim rw As Range
Set shmem = Sheets("SHEET1")
Set sh = Sheets("SHEET2")
For Each rw In sh.Rows
    If sh.Cells(rw.Row, 1).Value = "" And sh.Cells(rw.Row, 2).Value = "" Then
        Exit For
    End If

    With Application.WorksheetFunction
        Dim bdaytest As Variant
        Dim match1 As Double

        bdaytest = .Index((shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)) * (shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)), 0)
        'match1 = .Match(1, .Index((shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)) * (shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)), 0), 0)
        bdaytest = .Index(1, shmem.Range("D2:D121"), match1)
    End With
Next rw

错误发生在我从第 2 行中提取的下一行(现在注释掉)

bdaytest = .Index((shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)) * (shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)), 0)
'match1 = .Match(1, .Index((shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)) * (shmem.Range("A2:A121") = sh.Cells(rw.Row, 1)), 0), 0)

我知道错误必须发生，因为 bdaytest 是错误的数据类型，但我不确定，到目前为止我找不到任何解决方案。在此先感谢您的任何建议。

编辑:我想找出 2 列(A 和 B)具有请求值的行的行号。请求的值位于 sh.Cells(rw.Row, 1)和 sh.Cells(rw.Row, 2)

Answer 1

您不能使用 = 创建数组和 *就像在 VBA 中一样，不像在公式中。你可以做的是使用Application.Countifs像这样:

Dim sh As Worksheet
Dim shmem             As Worksheet
Dim rw                    As Range

Set shmem = Sheets("SHEET1")
Set sh = Sheets("SHEET2")

For Each rw In sh.Rows
    If sh.Cells(rw.Row, 1).Value = "" And sh.Cells(rw.Row, 2).Value = "" Then
        Exit For
    End If

    With Application
        Dim bdaytest      As Variant
        Dim match1        As Double

        bdaytest = .Match(1, .CountIfs(sh.Cells(rw.Row, 1), shmem.Range("A2:A121"), _
                                        sh.Cells(rw.Row, 2), shmem.Range("B2:B121")), 0)
        If Not IsError(bdaytest) Then bdaytest = shmem.Range("D2:D121").Cells(bdaytest)
    End With
Next rw

注: WorksheetFunction.Countifs不管用。