VBA Excel: Perform index match dynamically based on a config Sheet v2

Hello experts,

I have this excellent VBA code which allows me to perform index match actions through a config Sheet.

Option Explicit

Sub RunIndexMatch_revised()
    Dim wsConfig As Worksheet, wsResult As Worksheet, wsTargetSheet As Worksheet
    Dim sSheetName As String, sTargetColumn As String, sCompareColumn As String, sMatchColumn As String, sDestinationColumn, sSheetName2 As String
    Dim rgTarget As Range, rgCompare  As Range, rgMatch As Range, c As Range
    Dim rw As Integer, MaxRowTargetSheet As Long
    Dim vMatchRow As Variant
    
    CheckConfigSheet
    
    Set wsConfig = Worksheets("Config")
    Set wsResult = Worksheets("Result")
    Application.ScreenUpdating = False
    For rw = 2 To wsConfig.Range("A1").CurrentRegion.Rows.Count
        sSheetName = wsConfig.Range("A" & rw)
        Set wsTargetSheet = Worksheets(sSheetName)
        sSheetName2 = wsConfig.Range("F" & rw)
        Set wsResult = Worksheets(sSheetName2)
        MaxRowTargetSheet = wsTargetSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        sTargetColumn = wsConfig.Range("B" & rw)
        sCompareColumn = wsConfig.Range("C" & rw)
        sMatchColumn = wsConfig.Range("D" & rw)
        sDestinationColumn = wsConfig.Range("E" & rw)
        wsTargetSheet.Select
        Set rgTarget = wsTargetSheet.Range(sTargetColumn & "2:" & sTargetColumn & MaxRowTargetSheet)
        Set rgMatch = wsTargetSheet.Range(sMatchColumn & "2:" & sMatchColumn & MaxRowTargetSheet)
        wsResult.Select
        Set rgCompare = wsResult.Range(sCompareColumn & "2", Range(sCompareColumn & Rows.Count).End(xlUp))
        wsResult.Range(sDestinationColumn & "2:" & sDestinationColumn & (rgCompare.Rows.Count + 1)).ClearContents
        For Each c In rgCompare
            vMatchRow = Application.Match(c, rgMatch, 0)
            If IsNumeric(vMatchRow) Then
                wsResult.Range(sDestinationColumn & c.Row) = Application.WorksheetFunction.Index(rgTarget, vMatchRow, 1)
            End If
        Next c
    Next rw
End Sub

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



I would like to add two additional requirements based on values reported in column G and H.

2015-09-23-17_49_05-Microsoft-Excel-.png
1.If value reported in column G is equal to 0 perform the index match without any change. If flag is equal to 1 perform just the index match for the destination values which doesn't have information reported empty values, this means that the index match should be skipped for the values already filled in Destination column.
 It would be great to add a verification loop if the value is not equal to 1 or 0

 2.Report the count in column H related to the indexmatch values performed in Destination column.


Thank you very much for your help.
Dynamic-index-match-3.xlsm
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:
LD16Author Commented:
Thank  you for this code however I have a problem concerning the flag equal 0. Probably I didn't specified properly.
2015-09-25-00_59_12-Microsoft-Excel-.pngWhen I enter the flag = 0 in my Config Sheet and I enter manually test data in Destination Column Result-2 the macro didn't replace those values and it should replace them and don't skip them so the count is wrong.
2015-09-25-00_59_56-Microsoft-Excel-.png
18 values hasn't been replaced as the 3 values I entered manually before calling the macro
I think that the best is adapt the macro with the following:
If flag = 0 reset destination  range  and then perform the index match as when flag = 0 we don't want to omit anything we launch the index match without any condition.

I test the flag=1 and it works properly as it perform the index match just for the destination empty values.

Thank you very much for your help.
LD16Author Commented:
I was able to adapt based on my need by modifying the flag condition. Let me know if you think there is another better way to do this.
Dynamic-index-match-4.xlsm
Ejgil HedegaardCommented:
The initial flag condition 0 was just count possible matches.
Now it is:  Replace (or insert if empty) where there is a match, but keep the others.

That is not what you specify.
If flag = 0 reset destination  range  and then perform the index match as when flag = 0 we don't want to omit anything we launch the index match without any condition.
That is like the original method, before flag condition.
If you want that, the entire range values must be deleted before match.
insert this between the lines "Set rgCompare = ...." and "For Each c In rgCompare"
        If Flag = 0 Then
            wsResult.Range(sDestinationColumn & "2:" & sDestinationColumn & (rgCompare.Rows.Count + 1)).ClearContents
        End If

Open in new window


Delete the line
wsResult.Range(sDestinationColumn & c.Row).Clear
Since the value is replaced, there is no need to clear the cell first.

You could have several conditions for Flag
0: Just count, don't change anything.
1: Insert match values in empty cells, don't change the others (=as it is now).
2: Replace (or insert if empty) where there is a match, but keep the others (=condition 0 now).
3: Delete all values, and insert where there is a match (the original method).

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:
It works, thank you again. Excellent explanation!
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.