(VBA) Index & Match Two Worksheets

[GSanchez]

Hi Everyone!

My latest project involves going through over 100 worksheets in a single workbook and taking out specific information. The good thing is that all of these worksheets have the same headers so I thought of creating a macro that would use index and match.

As a test to see if this macro would work, I created a dummy workbook that contains 4 worksheets.

These worksheets names and purpose are as follows (I have also included a picture of each sheet except for Finder Part 2 as it is nearly the same as Finder Part 1)
1) Name: "Macro Holder" and the Purpose = will have four columns containing the necessary information where the macro will pull from in order to use worksheetfunction.index or worksheetfunction.match
2) Name: "Sheet1" and the Purpose = will be where the macro will post their results.
3) Name: "Finder Part 1" and the Purpose = contains the data where the macro will look into to pull a specific cell value if it fulfills the criteria of the index and match.
4) Name: "Finder Part 2" and the Purpose = contains the data where the macro will look into to pull a specific cell value if it fulfills the criteria of the index and match.

So I am trying to create a macro that will use index and match. The criteria for index and match will be found in "Macro Holder"
Under Column A we have "The Index" and underneath that we have an array "B2:F10" --> This will be used for worksheetfunction.index
Under Column B we have "RowLookup" and underneath that we have the cell value "Jul" --> This will be used for worksheetfunction.match(lookup value)
Under Column C we have "RowArray" and underneath that we have an array "A:A" --> This will be used for worksheetfunction.match(lookup array)
Under Column D we have "ColumnLookup" and underneath that we have the cell value "2018" --> This will be used for the second worksheetfunction.match(lookup value)
Under Column E we have "ColumnArray" and underneath that we have an array "A1:F1" --> This will be used for the second worksheetfunction.match(lookup array)

When I run the code I seem to be getting an error "Method 'Range' of object' _Global' failed "

Not sure how to fix it and so any help will be appreciated!

The vba code I have is seen below.

Sub Testing_WorksheetFunction()

Dim WS As Worksheet
Dim RNG As Variant

Dim TheIndex As Variant
Dim ColumnLookup As Variant
Dim ColumnArray As Variant
Dim RowLookup As Variant
Dim RowArray As Variant


TheIndex = Worksheets("Macro Holder").Range("A2").Value
ColumnLookup = Worksheets("Macro Holder").Range("D2").Value
ColumnArray = Worksheets("Macro Holder").Range("E2").Value
RowLookup = Worksheets("Macro Holder").Range("B2").Value
RowArray = Worksheets("Macro Holder").Range("C2").Value


    For Each WS In ThisWorkbook.Sheets
        If WS.Name <> "Macro Holder" And WS.Name <> "Sheet1" Then
           Set RNG = WorksheetFunction.Index(Range(TheIndex), _
           WorksheetFunction.Match(Range(RowLookup), Range(RowArray), 0), WorksheetFunction.Match(Range(ColumnLookup), Range(ColumnArray), 0))

    If Not RNG Is Nothing Then
        Sheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1).Value = RNG.Value
    Else
        Sheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1) = "Nothing"
    End If

End If
Next WS


End Sub

 

[GSanchez]

I modified it a tad, but now the error is "Mismatch" Error'13

I changed WorksheetFunction --> Application and I noticed that I used Range(RowLookup) & Range(ColumnLookup), but "Range" is not needed for those two so I removed it.

Still not sure where the mismatch is occurring. Any ideas?

Sub Testing_WorksheetFunction()

Dim WS As Worksheet
Dim RNG As Variant

Dim TheIndex As Variant
Dim ColumnLookup As String
Dim ColumnArray As Variant
Dim RowLookup As String
Dim RowArray As Variant


TheIndex = Worksheets("Macro Holder").Range("A2").Value
ColumnLookup = Worksheets("Macro Holder").Range("D2").Value
ColumnArray = Worksheets("Macro Holder").Range("E2").Value
RowLookup = Worksheets("Macro Holder").Range("B2").Value
RowArray = Worksheets("Macro Holder").Range("C2").Value


    For Each WS In ThisWorkbook.Sheets
        If WS.Name <> "Macro Holder" And WS.Name <> "Sheet1" Then
           Set RNG = Application.Index(Range(TheIndex), _
           Application.Match((RowLookup), Range(RowArray), 0), Application.Match((ColumnLookup), Range(ColumnArray), 0))

    If Not RNG Is Nothing Then
        Sheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1).Value = RNG.Value
    Else
        Sheets("Sheet1").Range("A1").End(xlToRight).Offset(0, 1) = "Nothing"
    End If

End If
Next WS


End Sub