使用Match返回列号

Question

尝试使用.Match来识别搜索一行的列值。

基本上，我有3个工作表，每个工作表都有不同的标题，我希望从每个工作表中找出哪些标题是相同的，以便比较数据。

这是我到目前为止所拥有的代码(目前它只是一个PoC )：

Sub aaaa()

Dim a As Double
Dim b As Integer
Dim c As Integer
Dim d As Variant
Dim e As Variant
Dim f As Variant
Dim h As Double

Worksheets("Reconciliation").Activate

Columns.Select
Selection.ClearContents

a = 1
h = 2

While Worksheets("Cleanse").Cells(a, 1) <> vbNullString

    b = 1
    c = 1
    d = vbNullString
    e = vbNullString
    f = vbNullString

    While Worksheets("Cleanse").Cells(a, 1) <> vbNullString

        If a = 1 Then
            Cells(h, b) = Worksheets("Cleanse").Cells(a, c)
            Cells(h, b + 1) = Worksheets("Cleanse").Cells(a, c) & "_CUMIS"

            d = WorksheetFunction.Match(Worksheets("Cleanse").Cells(a, c), Worksheets("MAddress").Rows("1:1"), 0)

            If Not IsError(d) Then

                Cells(h - 1, b + 1) = d

            ElseIf IsError(d) Then

                d = WorksheetFunction.Match(Worksheets("Cleanse").Cells(a, c), Worksheets("Member_Details").Rows(1), 0)
                Cells(h - 1, b + 1) = "M" & d

            End If

            b = b + 2
            c = c + 1

        Else
            Cells(h, b) = Worksheets("Cleanse").Cells(a, c)

            If b = 1 Then
                e = WorksheetFunction.Match(Worksheets("Cleanse").Cells(a, c), Worksheets("MAddress").Range("A:A"), 0)


                If Not IsError(e) And Not IsError(d) Then

                    Cells(a, b + 1) = Worksheets("MAddress").Cells(d, e)

                Else
                End If
            End If



        End If

    Wend

    a = a + 1

Wend

End Sub

当我关闭它时，我得到了以下错误：

Run-time error '9': Subscription out of range

关于如何在行中搜索变量值并返回列number>的建议

Answer 1

如果你想用IsError(d)测试d，那么你必须使用d= application.match(...)，而不是d= worksheetFunction.match(...)。

        d = Application.Match(Worksheets("Cleanse").Cells(a, c), Worksheets("MAddress").Rows("1:1"), 0)

        If Not IsError(d) Then
            Cells(h - 1, b + 1) = d
        Else
            d = Application.Match(Worksheets("Cleanse").Cells(a, c), Worksheets("Member_Details").Rows(1), 0)
            If Not IsError(d) Then
                Cells(h - 1, b + 1) = "M" & d
            end if
        End If
        ...