(VBA) Index & Match Two Worksheets

GSanchez

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Attachments

  • Macro Holder.PNG
    Macro Holder.PNG
    23.4 KB · Views: 62
  • sheet1.PNG
    sheet1.PNG
    22.5 KB · Views: 61
  • Finder Part 1.PNG
    Finder Part 1.PNG
    24.4 KB · Views: 62

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,705
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top