Range versus Range Cells using Index Match in VBA

Question

I'm struggling to use the Range Cells method with Index Match in VBA. Using standard ranges works fine, but I'm having no luck with Range Cells. I must be failing to grasp something fundamental about Index Match. I have commented out the lines of code that fail. I appreciate any pointers the community can provide.

    Sub IndexMatchTroubleShooting()

'dim worksheets
Dim Source As Worksheet
Dim Target As Worksheet

'set worksheets
Set Source = ThisWorkbook.Sheets("Source")
Set Target = ThisWorkbook.Sheets("Target")

'dim ranges
Dim ValuesToPull As Range
Dim TargetIDs As Range
Dim SourceIDs As Range
Dim MyRange As Range

'using range <-this works
Set ValuesToPull = Source.Range("B1:B5682")
Set TargetIDs = Target.Range("A1:A21")
Set SourceIDs = Source.Range("A1:A5682")
Set MyRange = Target.Range("B1:B21")

'using range cells <-this produces this error: "Run-time Error 1004 Method 'Range' of object '_Worksheet' failed"
'Set ValuesToPull = Source.Range(Cells(1, 2), Cells(5682, 2))
'Set TargetIDs = Target.Range(Cells(1, 1), Cells(21, 1))
'Set SourceIDs = Source.Range(Cells(1, 1), Cells(5682, 1))
'Set MyRange = Target.Range(Cells(1, 2), Cells(21, 2))

'apply formula
MyRange = Application.Index(ValuesToPull, Application.Match(TargetIDs, SourceIDs, 0))

End Sub

Answer 1

You need to fully qualify all Range/Cells references with a sheet as this will error if a different sheet is active when the macro is run, e.g.

Set ValuesToPull = Source.Range(Source.Cells(1, 2), Source.Cells(5682, 2))

or to save a bit of typing

With Source
    Set ValuesToPull = .Range(.Cells(1, 2), .Cells(5682, 2))
    Set SourceIDs = .Range(.Cells(1, 1), .Cells(5682, 1))
End With
With Target
    Set TargetIDs = .Range(.Cells(1, 1), .Cells(21, 1))
    Set MyRange = .Range(.Cells(1, 2), .Cells(21, 2))
End With

(Not sure you can use a multi-cell range in a match formula like that - does anyone know?)

Answer 2

    Sub MatchMaster()

'this script helps simplify the use of Excel's Index Match function
'place this script in your personal macro workbook and assign it to a button
'use it to pull data between two worksheets that share unique identifiers

'dim ranges
Dim ValuesToPull As Range
Dim TargetIDs As Range
Dim SourceIDs As Range
Dim MyRange As Range

'dim worksheets
Dim Source1 As Worksheet
Dim Target1 As Worksheet
Dim Source2 As Worksheet
Dim Target2 As Worksheet

'input box dims
Dim Prompt1 As String
Dim Prompt2 As String
Dim Prompt3 As String
Dim Prompt4 As String
Dim Title1 As String
Dim Title2 As String
Dim Title3 As String
Dim Title4 As String

'set prompts
Prompt1 = "Select values to pull (1 column only)"
Prompt2 = "Select unique IDs on target sheet (1 column only)"
Prompt3 = "Select unique IDs on source sheet (1 column only)"
Prompt4 = "Where should we put these values? (1 column only)"

'set titles
Title1 = "Source Sheet"
Title2 = "Target Sheet"
Title3 = "Source Sheet"
Title4 = "Target Sheet"

'error handling
On Error GoTo OuttaHere

'input boxes
Set SourceIDs = Application.InputBox(Prompt3, Title3, Type:=8)
Set Source1 = SourceIDs.Worksheet
SourceIDcolumn = SourceIDs.Column
LastSourceID = Source1.Cells(Rows.Count, SourceIDcolumn).End(xlUp).Row
Source1.Activate

Set ValuesToPull = Application.InputBox(Prompt1, Title1, Type:=8)
Set Source2 = ValuesToPull.Worksheet
ValuesColumn = ValuesToPull.Column
LastValue = Source2.Cells(Rows.Count, ValuesColumn).End(xlUp).Row
Source2.Activate

Set TargetIDs = Application.InputBox(Prompt2, Title2, Type:=8)
Set Target1 = TargetIDs.Worksheet
TargetIDcolumn = TargetIDs.Column
LastTargetID = Target1.Cells(Rows.Count, TargetIDcolumn).End(xlUp).Row '<~~ also use this for MyRange
Target1.Activate

Set MyRange = Application.InputBox(Prompt4, Title4, Type:=8)
Set Target2 = MyRange.Worksheet
MyColumn = MyRange.Column
Target2.Activate

'convert input to Range Cells format
With Source1
    Set SourceIDs = .Range(.Cells(1, SourceIDcolumn), .Cells(LastSourceID, SourceIDcolumn))
End With

With Source2
    Set ValuesToPull = .Range(.Cells(1, ValuesColumn), .Cells(LastValue, ValuesColumn))
End With

With Target1
     Set TargetIDs = .Range(.Cells(1, TargetIDcolumn), .Cells(LastTargetID, TargetIDcolumn))
End With

With Target2
     Set MyRange = .Range(.Cells(1, MyColumn), .Cells(LastTargetID, MyColumn))
End With

'apply formula
MyRange = Application.Index(ValuesToPull, Application.Match(TargetIDs, SourceIDs, 0))

OuttaHere:
ActiveWorkbook.ActiveSheet.Columns.AutoFit

End Sub