trjhtr

I find this script handy for preparing ad-hoc reporting. Using two sets of unique identifiers, it allows you to lookup multiple columns of data with the Index Match function. I welcome any feedback or suggestions for improvement.

Sub MatchMaster_PRO()

'Peter Domanico (June 2018)
'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 or more columns)"
 Prompt2 = "Select unique IDs on target sheet (1 column only)"
 Prompt3 = "Select unique IDs on source sheet (1 column only)"
 Prompt4 = "Select any range on target sheet"

'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
 LastValue = LastSourceID
 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 Target1
 Set TargetIDs = .Range(.Cells(1, TargetIDcolumn), .Cells(LastTargetID, TargetIDcolumn))
 End With

 Dim rng As Range
 For Each rng In ValuesToPull.Columns
 ValuesColumn = rng.Column
 NextColumn = Target2.Cells(1, Columns.Count).End(xlToLeft).Column + 1
 With Source2
 Set ValuesToPull = .Range(.Cells(1, ValuesColumn), .Cells(LastValue, ValuesColumn))
 End With
 With Target2
 Set MyRange = .Range(.Cells(1, NextColumn), .Cells(LastTargetID, NextColumn))
 End With
 MyRange = Application.index(ValuesToPull, Application.Match(TargetIDs, SourceIDs, 0))
 Next rng

OuttaHere:
 ActiveWorkbook.ActiveSheet.Columns.AutoFit

End Sub

VBE -> Menu Bar -> Tools -> Options

I recommend turning off [ ] Auto Syntax Check and turning on [x] Require Variable Declaration

Turning off Auto Syntax Check will stop those annoying messages from popping up when you are writing code. The VB Editor highlights syntax errors in red anyway. The messages do nothing but break the workflow and slow you down.

Require Variable Declaration inserts Option Explicit at the top of newly created code modules. Option Explicit requires that all variables be declared. Ideally, all variable should be declared and strongly typed.

Declaring Variables

These variables were not declared:

Dim LastSourceID As Long, LastTargetID As Long, LastValue As Long, MyColumn As Long, NextColumn As Long, TargetIDcolumn As Long, ValuesColumn As Long, SourceIDcolumn As Long

I prefer grouping my variables (at the top of the procedure), roughly 1 Dim statement per datatype. I do not like having to scroll to see what the code is doing.

The first thing that stands out is that the Title and Prompt variables are constant values and should be declared as Constants.

'set prompts
Const Prompt1 As String = "Select values to pull (1 or more columns)"
Const Prompt2 As String = "Select unique IDs on target sheet (1 column only)"
Const Prompt3 As String = "Select unique IDs on source sheet (1 column only)"
Const Prompt4 As String = "Select any range on target sheet"

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

Constants can also be grouped but I usually don't because it can make them difficult to reference and modify.

'set prompts
Const Prompt1 As String = "Select values to pull (1 or more columns)",Prompt2 As String = "Select unique IDs on target sheet (1 column only)", Prompt3 As String = "Select unique IDs on source sheet (1 column only)",Prompt4 As String = "Select any range on target sheet"

'set titles
Const Title1 As String = "Source Sheet", Title2 As String = "Target Sheet", Title3 As String = "Source Sheet",Title4 As String = "Target Sheet"

Helper Variables

"Helper Variables" are great when they are helpful. Many times they are just extra clutter that you have to work through.

Prompt1 is helpful because of the length of the string and these is something that you will probably consider modifying in the future.

Const Prompt1 As String = "Select values to pull (1 or more columns)"

Title1 is pretty short and easily modified where it is used. I would not have bothered with it.

Set ValuesToPull = Application.InputBox(Prompt1, "Source Sheet", Type:=8)

I personally would save the space and use one of the following:

Set ValuesToPull = Application.InputBox("Select values to pull (1 or more columns)", "Source Sheet", Type:=8)


Set ValuesToPull = Application.InputBox( _
 "Select values to pull (1 or more columns)", _
 "Source Sheet" _
 , Type:=8)

Set ValuesToPull = Application.InputBox( _
 Prompt:="Select values to pull (1 or more columns)", _
 Title:="Source Sheet" _
 , Type:=8)

Are Source1, Target1, SourceIDcolumn, and LastSourceID really needed?

I like to use the Optometrist Analogy when reviewing code.

Which is clearer, Code 1 or Code 2?

Code 1:

With Source1
 Set SourceIDs = .Range(.Cells(1, SourceIDcolumn), .Cells(LastSourceID, SourceIDcolumn))
End With

Code 2:

With SourceIDs.Worksheet
 Set SourceIDs = .Range(.Cells(1, SourceIDcolumn), .Cells(LastSourceID, SourceIDcolumn))
End With

Which is clearer, Code 2 or Code 3?

Code 3:

Set ValuesToPull = Intersect(SourceIDs.EntireRow, ValuesToPull.EntireColumn)

For me Code 3 is clearer. But not everyone sees code the same way. You should use the one that is easiest for you to read. Note: If you didn't choose Code 3, you might want to consider Lasik.

Repeated Code Patterns

Repeated code patterns in a subroutine is a sign that potions of the code can and probably should be extracted into a separate subroutine. This will make both the main and extracted code easier to read, debug, and modify.

Public Function getInputRange(Prompt As String, Title As String, Optional AllowMultipleColumns As Boolean) As Range
 Dim Target As Range
 Set Target = Application.InputBox(Prompt, Title, Type:=8)

 If Target Is Nothing Then Exit Function
 If Target.Columns.Count > 1 And Not AllowMultipleColumns Then Exit Function

 With Target.EntireColumn
 Set Target = Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
 End With

 Set getInputRange = Target
End Function

Other Considerations

Are there Column Headers?
hasColumnHeader = MsgBox("Do the Ranges Contain Column Headers?", vbYesNo, "Column Header")

Rows.Count should be qualified to the same worksheet the Target1.

Target1.Cells(Rows.Count

Performance

Turning off Application.Application.ScreenUpdating and setting Calculation = xlCalculationManual will greatly improve the performance of code that modify the formats and values of worksheets.

Public Sub SpeedBoost(TurnOn As Boolean)
 With Application
 .Calculation = IIf(TurnOn, xlCalculationManual, xlCalculationAutomatic)
 .ScreenUpdating = Not TurnOn
 End With
End Sub

Reading values from an array is a little faster than reading values from a range. Writing multiple values from an array to a range in 1 operating is much faster than multiple writes.

Dictionaries are much faster than Application.Match. Reference:Analyst Cave: EXCEL VLOOKUP VS INDEX MATCH VS SQL VS VBA