VBA: simplify index match loops.

I have the following code:

Sub indexmatch ()

Set Target = Range("ccprojectstable!v2:v" & Rows.Count)
Set matchvalue = Range("E2", Range("E" & Rows.Count).End(xlUp))
Set comparedvalue = Range("ccprojectstable!A2:A" & Rows.Count)

For Each c In matchvalue

If  IsNumeric(Application.Match(c, comparedvalue, 0)) Then
c.Offset(, -3).Value = Application.WorksheetFunction.Index(Target, Application.WorksheetFunction.Match(c, comparedvalue, 0), 0)
End If
Next c



'--------CP---------

Set Target = Range("accountingtable!F2:F" & Rows.Count)
Set matchvalue = Range("E2", Range("E" & Rows.Count).End(xlUp))
Set comparedvalue = Range("accountingtable!A2:A" & Rows.Count)

For Each c In matchvalue

If  IsNumeric(Application.Match(c, comparedvalue, 0)) Then
c.Offset(, 10).Value = Application.WorksheetFunction.Index(Target, Application.WorksheetFunction.Match(c, comparedvalue, 0), 0)
End If
Next c

End Sub

Open in new window



I would like to:
-Optimize this Sub by defining just one for each  (and not two)
-Define the variables in another sheet (Target, match value, compared Value), instead of modifying the code and Add another loop every time that I want to return a value.
-Replace c.Offset by the destination column in which I want to return the values.

Thank you again for your help.
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Use a sub parsing the arguments
Sub DoIndexMatch(Target As Range, matchvalue As Range, comparedvalue As Range, DestinationColumn As String)
    Dim c As Range
    For Each c In matchvalue
        If IsNumeric(Application.Match(c, comparedvalue, 0)) Then
            Range(DestinationColumn & c.Row) = Application.WorksheetFunction.Index(Target, Application.WorksheetFunction.Match(c, comparedvalue, 0), 0)
        End If
    Next c
End Sub

Open in new window


Then call the sub like this, where you can add more lines for other matches.
Sub RunIndexMatch()
    DoIndexMatch Range("ccprojectstable!v2:v" & Rows.Count), Range("E2", Range("E" & Rows.Count).End(xlUp)), Range("ccprojectstable!A2:A" & Rows.Count), "B"
    DoIndexMatch Range("accountingtable!F2:F" & Rows.Count), Range("E2", Range("E" & Rows.Count).End(xlUp)), Range("accountingtable!A2:A" & Rows.Count), "O"
End Sub

Open in new window

LD16Author Commented:
Excellent proposal, thank you for this comment.

1-Instead of entering in the Code the ranges variables is there a way to defined them in another sheet such as config-sheet in which we can have:
2015-07-03-22-59-12-Microsoft-Excel-non-
Could you please help me to define the DoIndexMatch with the information reported in config-sheet?

2-More complex I think, instead of DoIndexMatch two times is not a way to do a unique DoIndexMatch and read all the lines defined in config-sheet?
If 2 is not possible, no problem I will entering multiple times DoIndexMatch however it would be great to conver 1-.

Thank you again for your help.
Ejgil HedegaardCommented:
Now there are only 2 lines in the config-sheet, but with more I think it will be too complicated to read all the lines and do the match for all at once.
A For..Next loop can read the lines one at a time, and is much easier to maintain.
I can't test it because I don't have any data.

Sub RunIndexMatch()
    Dim wsConfig As Worksheet
    Dim sSheetName As String, sTargetColumn As String, sCompareColumn As String, sMatchColumn As String, sDestinationColumn As String
    Dim rgTarget As Range, rgCompare  As Range, rgMatch As Range
    Dim rw As Integer
    Set wsConfig = Worksheets("Config")
    For rw = 2 To wsConfig.Range("A1").CurrentRegion.Rows.Count
        sSheetName = wsConfig.Range("A" & rw)
        sTargetColumn = wsConfig.Range("B" & rw)
        sCompareColumn = wsConfig.Range("C" & rw)
        sMatchColumn = wsConfig.Range("D" & rw)
        sDestinationColumn = wsConfig.Range("E" & rw)
        rgTarget = Range(sSheetName & "!" & sTargetColumn & "2:" & sTargetColumn & Rows.Count)
        rgCompare = Range(sCompareColumn & "2", Range(sCompareColumn & Rows.Count).End(xlUp))
        rgMatch = Range(sSheetName & "!" & sMatchColumn & "2:" & sMatchColumn & Rows.Count)
        DoIndexMatch rgTarget, rgCompare, rgMatch, sDestinationColumn
    Next rw
End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LD16Author Commented:
Thank you for this comment.

I have tested and I have an error message for the Target sheet:

Object variable or With block variable not set

Please find bellow my workbook test.
Dynamic-index-match.xlsm
Ejgil HedegaardCommented:
Forgot to use the Set command for the ranges, and often it is also needed to select the sheet before the ranges are set.

I have changed the specification on the Config sheet, = switched the 2 sheets in column A to fit the specification in your initial post here.
Check that.

Deleted DoIndexMatch and made the function inside the sub.

Added a max row count for the target sheet to only do the match on the actual used rows, and not the entire sheet rows.
The result columns are cleared before new data applied, delete the line or comment it if you don't want that.
The variant variable vMatchRow is used so the match is only performed once, and not twice when a match is found, increase speed.
Application.ScreenUpdating = False also increase speed.

"Option Explicit" at the top tells VBA that variables must be declared.
I always use that to avoid misspelling, and it also make coding easier when variables are declared.
Type a few letters of the variable name, use Ctrl+Space and a list is shown, or if only one the variable inserted.
Go to Tools-Options and mark "Require Variable Declaration", then it is automatically added to any new module.
Dynamic-index-match.xlsm
LD16Author Commented:
Excellent, thank you very much for this solution and for the explanation!!

I have also added more than 2 line and it works perfectly! Additionally as you did for the Target / Comparedvalue Sheet I have also added another Column for Desination Sheet (F). This will allows me do dynamically perform  index Match on multiple Result Sheets.

Now the last question is the error check. Do you think if is there a way to add an if err then msgBox("Warning Data entered in Config Sheet is not consistent, please check that:
1-Target/Comparedvalue and Destination  Sheets exist or there is a misspelled mistake or you haven't entered data.
2-Required columns entered in Range(B:E) are alphabetical  and not numeric."?

Thank you again for your help.
LD16Author Commented:
Sorry I forgot the last version of test file which include the Destination Sheet.

Please find attached the file.
Dynamic-index-match-2.xlsm
Ejgil HedegaardCommented:
Like this

Sub CheckConfigSheet()
    Dim wsConfig As Worksheet, ws As Worksheet, rw As Integer, col As Integer, i As Integer, WarningText As String
    Set wsConfig = Worksheets("Config")
    For rw = 2 To wsConfig.Range("A1").CurrentRegion.Rows.Count
        i = 0
        For Each ws In Worksheets
            If wsConfig.Range("A" & rw) <> "" Then
                If UCase(ws.Name) = UCase(wsConfig.Range("A" & rw)) Then
                    i = i + 1
                End If
                If UCase(ws.Name) = UCase(wsConfig.Range("F" & rw)) Then
                    i = i + 1
                End If
            End If
        Next ws
        For col = 2 To 5
            If wsConfig.Cells(rw, col) <> "" And Len(wsConfig.Cells(rw, col)) <= 2 Then
                If WorksheetFunction.IsText(wsConfig.Cells(rw, col)) Then
                    i = i + 1
                End If
            End If
        Next col
        If i <> 6 Then
            WarningText = "Warning" & Chr(10) & "Data entered in Config Sheet row " & CStr(rw) & " is not consistent, please check that:"
            WarningText = WarningText & Chr(10) & "1-Target/Comparedvalue and Destination  Sheets exist or there is a misspelled mistake or you haven't entered data."
            WarningText = WarningText & Chr(10) & "Required columns entered in Range(B:E) are alphabetical and not numeric."
            WarningText = WarningText & Chr(10) & Chr(10) & "Program stop"
            MsgBox WarningText, vbCritical
            End
        End If
    Next rw
End Sub

Open in new window

Dynamic-index-match-2.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Amazing I have tested and it works.
And one more time I'm really impressed!!!
Thank you again for your help!!!
LD16Author Commented:
Excellent!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.