IF Statement Not Working - Need 2nd Pair of Eyes

[Sphinx404]

I cannot figure out why my IF statement won't populate the matches in the highlighted cells from the image above. Any thoughts?

Do Until Range("AQ" & row).Formula = ""


match1 = ""
match2 = ""
match3 = ""


code1 = Range("AQ" & row).Value
code2 = Range("AR" & row).Value
code3 = Range("AS" & row).Value


If code1 = Range("AT" & row).Value Then match1 = "MATCH" 'if AQ matches AT, then MATCH1 = match
If code1 = Range("AU" & row).Value Then match1 = "MATCH" 'if AQ matches AU, then MATCH1 = match
If code1 = Range("AV" & row).Value Then match1 = "MATCH" 'if AQ matches AV, then MATCH1 = match
If code1 = Range("AW" & row).Value Then match1 = "MATCH" 'if AQ matches AW, then MATCH1 = match
If code1 = Range("AX" & row).Value Then match1 = "MATCH" 'if AQ matches AX, then MATCH1 = match


If code2 = Range("AT" & row).Value Then match2 = "MATCH" 'if AR matches AT, then MATCH2 = match
If code2 = Range("AU" & row).Value Then match2 = "MATCH" 'if AR matches AU, then MATCH2 = match
If code2 = Range("AV" & row).Value Then match2 = "MATCH" 'if AR matches AV, then MATCH2 = match
If code2 = Range("AW" & row).Value Then match2 = "MATCH" 'if AR matches AW, then MATCH2 = match
If code2 = Range("AX" & row).Value Then match2 = "MATCH" 'if AR matches AX, then MATCH2 = match


If code3 = Range("AT" & row).Value Then match3 = "MATCH" 'if AS matches AT, then MATCH3 = match
If code3 = Range("AU" & row).Value Then match3 = "MATCH" 'if AS matches AU, then MATCH3 = match
If code3 = Range("AV" & row).Value Then match3 = "MATCH" 'if AS matches AV, then MATCH3 = match
If code3 = Range("AW" & row).Value Then match3 = "MATCH" 'if AS matches AW, then MATCH3 = match
If code3 = Range("AX" & row).Value Then match3 = "MATCH" 'if AS matches AX, then MATCH3 = match


If match1 = "MATCH" Then
    Range("BD" & row).Value = "MATCH"
    If match2 = "MATCH" Then
        Range("BE" & row).Value = "MATCH"
[COLOR=#ff0000]        If match3 = "MATCH" Then
            Range("BF" & row).Value = "MATCH"[/COLOR]
Else
    Range("BG" & row).Value = "NO MATCH"
        End If
    End If
End If
If Range("BD" & row).Value = "" Then
    If Range("BE" & row).Value = "" Then
        If Range("BF" & row).Value = "" Then
            Range("BG" & row).Value = "NO MATCH"
        End If
    End If
End If


row = row + 1


Loop

 

[Fluff]

Best way to find out why it's not working is to step through the code using F8 & see what happens & when.

In this case if match1 does not = MATCH your code will jump to the Else statement & ignore the nested Ifs

 

[Eric W]

Incidentally, you can replace that entire section of code with:

    For r = 2 To Cells(Rows.Count, "AQ").End(xlUp).Row
        Cells(r, "BD").Resize(1, 3).Value = Evaluate("IF(ISNUMBER(MATCH(" & Cells(r, "AQ").Resize(1, 3).Address & _
                                                     "," & Cells(r, "AT").Resize(1, 5).Address & ",0)),""MATCH"","""")")
                                                 
        Cells(r, "BG") = IIf(WorksheetFunction.CountIf(Cells(r, "BD").Resize(1, 3), "MATCH") = 0, "NO MATCH", "")
    Next r

 

[Sphinx404]

You guys are ridiculous.

I can't even interpret what your code says. However, I did try it out... and it works flawlessly. Which is bittersweet... On the one hand, I get to go home now and my weekend begins, and on the other hand, I have so much to learn..

Thank you both.

 

[Eric W]

I always enjoy learning new things, so it's nice to know that there's more to learn. In this case, I noticed that your code essentially does the same thing as a MATCH worksheet function. So the first line (after the For) just uses Evaluate to build and execute the MATCH function on the 3 cells you're interested in. The next line uses a different way to call a worksheet function from Excel, the COUNTIF.

If that's your code, you're probably better off sticking with something you understand, but just remember there's more than 1 way to skin a cat!

Glad we could help!