4
\$\begingroup\$

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
\$\endgroup\$
4
\$\begingroup\$

VBE -> Menu Bar -> Tools -> Options

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

VBE -> Tools -> Options

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

\$\endgroup\$
  • \$\begingroup\$ thank you! Your comment will be a valuable resource for me going forward. Cheers. \$\endgroup\$ – peter.domanico Jun 15 '18 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.