For, If, ElseIf, Else Application.Match Loop is Producing Incorrect Results

Question

I'm using Excel VBA code to help me sort and filter through multiple workbooks/sheets to be placed on a "MATCHES" sheet in a workbook holding the VBA code. This "MATCHES" workbook also contains another worksheet holding "SOURCES" data for the multiple worksheet locations in one or two worksheets per workbook for reviewed. I've come very far with my code, but I'm running into a head scratcher with results from a Application.Match not working as expected through a For, If, ElseIf, Else loop. Below is the code I have using an anchored sheet ("WB2") and two other sheets for now.

Sub MATCH()

' KEY: 
' L = LIVE worksheet
' T = TEST worksheet
' R = Range (LR, TR)
' B, C, F = Column of values to match (LB, TB, LC, TC, LF, TF)
' # (2,13,etc.) = number associated with SOURCES worksheet (LB2, TF13, TR13, etc.)
' MV = Match value
' MX = Maximum rows of data compared among all sheets
' AR = Active Row Number
' WB = Workbook

' Start position
ThisWorkbook.Worksheets("MATCHES").Activate
Cells(2, 1).Activate
' Calculate max rows among sheets
Dim MX1 As Range: Set MX1 = Worksheets("SOURCES").Range("D2:D17") 'LIVE worksheets
Dim MX2 As Range: Set MX2 = Worksheets("SOURCES").Range("D12:D17") 'TEST worksheets
Dim MX As Integer: MX = WorksheetFunction.Max(MX1, MX2) 'Max of 2 lines above

' Variables for 'Kept' worksheet
Dim Filename2 As Variant: Filename2 = Worksheets("SOURCES").Range("A2") & ".xlsx"
 If Len(Filename2) > 5 And Worksheets("SOURCES").Range("C2") = "YES" Then
    Dim WB2 As Workbook: Set WB2 = Workbooks(Filename2)
    Dim LB2 As Variant: LB2 = WB2.Worksheets("LIVE KEYS").Range("$B$1:$B$" & MX)
    Dim LC2 As Variant: LC2 = WB2.Worksheets("LIVE KEYS").Range("$C$1:$C$" & MX)
    Dim LF2 As Variant: LF2 = WB2.Worksheets("LIVE KEYS").Range("$F$1:$F$" & MX)
 End If

' Calculation Loop
For X = 2 To 5 'MX

  ' Active row number
  Dim AR As Integer: AR = ActiveCell.Row
  ' Match values from 'Kept' work sheet
  Dim MV(1 To 3) As Variant 'TEST KEPT FROM MERGE
  MV(1) = WB2.Worksheets("LIVE KEYS").Range("$B" & AR)
  MV(2) = WB2.Worksheets("LIVE KEYS").Range("$C" & AR)
  MV(3) = WB2.Worksheets("LIVE KEYS").Range("$F" & AR)

MsgBox "THE SURVEY SAYS..."

' Variables for non-kept sheets and match operations
    Dim Filename13 As Variant: Filename13 = Worksheets("SOURCES").Range("A13") & ".xlsx"
    If Len(Filename13) > 5 And Worksheets("SOURCES").Range("C13") = "YES" Then
    ' Range to search on workbook's worksheet
    Dim WB13 As Workbook: Set WB13 = Workbooks(Filename13)
    Dim TB13 As Variant: TB13 = WB13.Worksheets("TEST KEYS").Range("$B$1:$B$" & MX)
    Dim TC13 As Variant: TC13 = WB13.Worksheets("TEST KEYS").Range("$C$1:$C$" & MX)
    Dim TF13 As Variant: TF13 = WB13.Worksheets("TEST KEYS").Range("$F$1:$F$" & MX)
    ' Match results for current sheet
    Dim M13F3 As Integer: On Error Resume Next: M13F3 = Application.MATCH(MV(3), TF13, 0)
    Dim M13C2 As Integer: On Error Resume Next: M13C2 = Application.MATCH(MV(2), TC13, 0)
    Dim M13B1 As Integer: On Error Resume Next: M13B1 = Application.MATCH(MV(1), TB13, 0)
    End If
  ' If below is "True" paste values from matched row (columns A-F) into "MATCHES" sheet
  If Not IsError(M13F3) And Not IsError(M13C2) And Not IsError(M13B1) And M13B1 = M13C2 And M13B1 = M13F3 And M13C2 = M13F3 Then
    Dim TR13 As Variant: TR13 = WB13.Worksheets("TEST KEYS").Range("$A$" & M13B1 & ":$F$" & M13B1)
    Worksheets("MATCHES").Range("$A$" & AR & ":$F$" & AR) = TR13
    MsgBox "MATCH : 13"
    MsgBox "NEXT!"

    Dim Filename14 As Variant: Filename14 = Worksheets("SOURCES").Range("A14") & ".xlsx"
    If Len(Filename14) > 5 And Worksheets("SOURCES").Range("C14") = "YES" Then
    ' Range to search on workbook's worksheet
    Dim WB14 As Workbook: Set WB14 = Workbooks(Filename14)
    Dim TB14 As Variant: TB14 = WB14.Worksheets("TEST KEYS").Range("$B$1:$B$" & MX)
    Dim TC14 As Variant: TC14 = WB14.Worksheets("TEST KEYS").Range("$C$1:$C$" & MX)
    Dim TF14 As Variant: TF14 = WB14.Worksheets("TEST KEYS").Range("$F$1:$F$" & MX)
    ' Match results for current sheet
    Dim M14F3 As Integer: On Error Resume Next: M14F3 = Application.MATCH(MV(3), TF14, 0)
    Dim M14C2 As Integer: On Error Resume Next: M14C2 = Application.MATCH(MV(2), TC14, 0)
    Dim M14B1 As Integer: On Error Resume Next: M14B1 = Application.MATCH(MV(1), TB14, 0)
    End If
  ' If below is "True" paste values from matched row (columns A-F) into "MATCHES" sheet
  ElseIf Not IsError(M14F3) And Not IsError(M14C2) And Not IsError(M14B1) And M14B1 = M14C2 And M14B1 = M14F3 And M14C2 = M14F3 Then
    Dim TR14 As Variant: TR14 = WB14.Worksheets("TEST KEYS").Range("$A$" & M14B1 & ":$F$" & M14B1)
    MsgBox "THE SURVEY SAYS..."
    Worksheets("MATCHES").Range("$A$" & AR & ":$F$" & AR) = TR14
    MsgBox "MATCH : 14"
    MsgBox "NEXT!"

  Else
    ' Match results for current sheet
    Dim M2F3 As Integer: On Error Resume Next: M2F3 = Application.MATCH(MV(3), LF2, 0)
    Dim M2C2 As Integer: On Error Resume Next: M2C2 = Application.MATCH(MV(2), LC2, 0)
    Dim M2B1 As Integer: On Error Resume Next: M2B1 = Application.MATCH(MV(1), LB2, 0)
    ' If "True" paste values from matched row (columns A-F) into "MATCHES" sheet
    If Not IsError(M2F3) And Not IsError(M2C2) And Not IsError(M2B1) And M2B1 = M2C2 And M2B1 = M2F3 And M2C2 = M2F3 Then
      Dim LR2 As Variant: LR2 = WB2.Worksheets("LIVE KEYS").Range("$A$" & M2B1 & ":$F$" & M2B1)
      Worksheets("MATCHES").Range("$A$" & AR & ":$F$" & AR) = LR2
      MsgBox "MATCH : KEPT"
    End If
    MsgBox "NEXT!"

  End If

Y = X + 1
Cells(Y, 1).Activate

Next
MsgBox "GAME OVER"

End Sub

The first loop works, but the second loop is matching values from a sheet where the values do not exist. On the second loop the first two workbooks should be skipped (as False) in the loop and the Else should be the results. - Btw, the Else workbook (referred to as "anchored sheet" above) is the sheet ("WB2") holding a bunch of merged data I have to unmerge in SQL db. So in other words, if a search for a value from this sheet pulls False in the other sheets then the value belongs with the "anchor" ("WB2") record. However, the first sheet is producing True where I've manually conducted a Find in Excel to verify it is NOT true, specifically this line...

Dim M13F3 As Integer: On Error Resume Next: M13F3 = Application.MATCH(MV(3), TF13, 0)

I have a screenshot that may or may not help.

Btw, I've added another image below of the front end of Excel to help with the big picture in case there are some better ways to approach my requirements.

I hope I provided enough information and not too much to troubleshoot this issue. Thanks in advance!

FYI - you only need to add On Error Resume Next once (unless you revert to normal error handling which you have not done). However, It doesn't appear that you need to do this at all here. You can just code for the negative outcome — urdearboy
You may want to look at indenting your code properly -- makes things much easier to follow — cybernetic.nomad
you write "On the second loop the first two workbooks should be skipped (as False) in the loop and the Else should be the results." If you step through the code with F8, can't you work out why that does not happen? Is the condition still TRUE? If so, what causes the condition to not be FALSE? Stepping through the code should illustrate that. — teylyn
Remove all of your On Error Resume Next and see what happens — Tim Williams
Every line like Dim M13F3 As Integer: On Error Resume Next: M13F3 = Application.MATCH(MV(3), TF13, 0) is a problem. If Application.MATCH does not find a match, it returns an Error Value, not a runtime error. So, use Dim M13F3 as Variantand drop the On Error Resume Next. The If Not IsError(M13F3) And ... And M13C2 = M13F3 Then is also an issue. VBA If does not short circuit evaluation, if IsError(...) is true then comparing that value to another will cause a runtime error. So, nest your If's - first the If Not IsError(...) And ... Then followed by If X = Y And ... Then — chris neilsen

DavidH DavidH · Accepted Answer · 2019-09-26T05:12:31

After a through reading of the code I did notice that you have declared: ThisWorkbook.Worksheets and probably should read Workbook.Sheet whereas WB2.Worksheets should probably read WB2.Sheet All of the declared variables should also match excel syntax spelling unless you have specifically named excel "Sheet" "WorkSheets" and "WorkBooks" check your code for Excel Name spelling errors.

For, If, ElseIf, Else Application.Match Loop is Producing Incorrect Results

1 Answers