Nested if loop

[hskis]

Hello, I am relatively new to VBA and am making a tool for work. In this particular part I am trying to pull the correct email from the sheet 'Active Contacts' by first looking for an exact match, then an exact match of the next column over (another contact name), then approximate match in the same order. The loop is supposed to bring the nestedif formula down and stop when there is no more data in the column (4). Right now the first cell is getting solved but the loop isn't moving down, it stays at Range("E3"). Let me know what I can do, thank you!

Sub NestedIFDYN()
    Range("E3").Select
    Dim i As Integer
    i = 3
    Do While Cells(i, 4).Value <> ""
    If ActiveCell.Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)<>0" Then
        ActiveCell.Formula = _
            "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)"
    ElseIf ActiveCell.Formula = "=VLOOKUP('Enter Data'!R[-1]C[3],'Active Contacts'!R1:R1048576,2,FALSE)<>0" Then
        ActiveCell.Formula = "=VLOOKUP('Enter Data'!R[-1]C[3],'Active Contacts'!R1:R1048576,2,FALSE)"
    Else
        ActiveCell.Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,TRUE)"
    End If
    i = i + 1
    Loop
End Sub
[End Code]

 

[Joe4]

Welcome to the Board!

Change all your references of ActiveCell to Cells(i,4)
And you can get rid of the first line, selecting E3

 

[hskis]

Thank you! I chose to reference Cells(i,5). The Loop is working now except it's returning only the last line: Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,TRUE)". It's now a problem with the Nested if statement. The goal of the nested if is to move onto the next rule if the vlookup returns a 0 or a #N/A but I know it shouldn't have just gone straight to the last option. Let me know if you can help.

Sub NestedIFDYN()
    Dim i As Integer
    i = 3
    Do While Cells(i, 4).Value <> ""
    If Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)<>0" Or Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)<>#N/A" Then
        Cells(i, 5).Formula = _
            "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)"
    ElseIf Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[3],'Active Contacts'!R1:R1048576,2,FALSE)<>0" Or Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[3],'Active Contacts'!R1:R1048576,2,FALSE)<>#N/A" Then
        Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[3],'Active Contacts'!R1:R1048576,2,FALSE)"
    Else
        Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,TRUE)"
    End If
    i = i + 1
    Loop
End Sub
[End Code]

 

[Joe4]

I need to step away for a while, but will come back to this later.

By the way, your code tags aren't quite right. See here for how to get them to work properly:
https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

 

[Joe4]

I don't think you are approaching this correctly. If I am understanding your correctly, I don't think you want to check if the formula in a cell is equal to another formula (that is what the structure of:

[COLOR=#333333]If Cells(i, 5).Formula = "=VLOOKUP('Enter Data'!R[-1]C[2],'Active Contacts'!R1:R1048576,2,FALSE)<>0"[/COLOR]

is trying to do).

I think you simply want to check to see whether a lookup formula finds a value, or returns an error, right? I think it would be best to skip all the else statements, and simply do a single function that uses nested IFERROR and VLOOKUP functions, something structured like:

=IFERROR(VLOOKUP1,IFERROR(VLOOKUP2,IFERROR(VLOOKUP3,"Not found")))

where VLOOKUP1, 2, and 3 represent each VLOOKUP formula.

To get the exact syntax you need for VBA, just turn on your Macro Recorder, and record yourself entering the formula in one of your cells.