Incorrect output with Nested IFs in vba

Question

I want "TRUE" output only if individual values are also equal besides the total values.I am just including a part of code for which I am not getting correct output. I am trying to get the output as attached but I am receiving incorrect output with the following code. Please help me understand my mistake:

Dim aPRTS, bNIMS,d19, d8, d25, ud, p19, p8, p25, du19, du8, du25, AudLastCol,AudLastRow As Long
For l = 2 To AudLastRow

    aPRTS = .Cells(l, AudLastCol).Value
    bNIMS = .Cells(l, NIMsLastCol).Value

    d19 = .Cells(l, Application.Match("Deployed(1.9)", .Range("A1:A" & AudLastCol), 0)).Value
    d8 = .Cells(l, Application.Match("Deployed (800)", .Range("A1:A" & AudLastCol), 0)).Value
    d25 = .Cells(l, Application.Match("Deployed (2.5)", .Range("A1:A" & AudLastCol), 0)).Value
    p8 = .Cells(l, Application.Match("Total-800-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    p19 = .Cells(l, Application.Match("Total-1900-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    p25 = .Cells(l, Application.Match("Total-2500-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    ud = .Cells(l, Application.Match("Deployed (Unassigned)", .Range("A1:A" & AudLastCol), 0)).Value

    du19 = d19 + ud
    du8 = d8 + ud
    du25 = d25 + ud

    If aPRTS = bNIMS Then
            If (p19 = d19) And (p8 = d8) And (p25 = d25) Then
                .Cells(l, AudLastCol + 1).Value = "TRUE"
                .Cells(l, AudLastCol + 3).Value = "No Action required."
            ElseIf (p19 = du19) And (p8 = d8) And (p25 = d25) Then
                .Cells(l, AudLastCol + 1).Value = "TRUE"
                .Cells(l, AudLastCol + 3).Value = "No Action required."
            ElseIf (p19 = d19) And (p8 = du8) And (p25 = d25) Then
                .Cells(l, AudLastCol + 1).Value = "TRUE"
                .Cells(l, AudLastCol + 3).Value = "No Action required."
            ElseIf (p19 = d19) And (p8 = d8) And (p25 = du25) Then
                .Cells(l, AudLastCol + 1).Value = "TRUE"
                .Cells(l, AudLastCol + 3).Value = "No Action required."
            Else
                .Cells(l, AudLastCol + 1).Value = "FALSE"
                .Cells(l, AudLastCol + 2).Value = "Check Manually"
                .Cells(l, AudLastCol + 3).Value = "Band wise Carrier Mismatch."
            End If
     End If
Next l

Do you mean you are not getting FALSE when you think you should? *'I am receiving incorrect output'* is pretty ambiguous. — , Apr 02 '18 at 14:40
Please see the attachment for sample i/p-o/p. O/P is coming out to be "TRUE" for both cases for me. — HobbyCoder, Apr 02 '18 at 16:31

score 0 · Answer 1 · answered Apr 02 '18 at 14:51

Use the below code:

Dim aPRTS, bNIMS, d19, d8, d25, ud, p19, p8, p25, du19, du8, du25, AudLastCol, AudLastRow As Long
For l = 2 To AudLastRow

    aPRTS = .Cells(l, AudLastCol).Value
    bNIMS = .Cells(l, NIMsLastCol).Value

    d19 = .Cells(l, Application.Match("Deployed(1.9)", .Range("A1:A" & AudLastCol), 0)).Value
    d8 = .Cells(l, Application.Match("Deployed (800)", .Range("A1:A" & AudLastCol), 0)).Value
    d25 = .Cells(l, Application.Match("Deployed (2.5)", .Range("A1:A" & AudLastCol), 0)).Value
    p8 = .Cells(l, Application.Match("Total-800-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    p19 = .Cells(l, Application.Match("Total-1900-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    p25 = .Cells(l, Application.Match("Total-2500-PRTS", .Range("A1:A" & AudLastCol), 0)).Value
    ud = .Cells(l, Application.Match("Deployed (Unassigned)", .Range("A1:A" & AudLastCol), 0)).Value

    du19 = d19 + ud
    du8 = d8 + ud
    du25 = d25 + ud

    If aPRTS = bNIMS Then
        If (p19 = d19 Or p19 = du19) And (p8 = d8 Or p8 = du8) And (p25 = d25 Or p25 = du25) Then
            .Cells(l, AudLastCol + 1).Value = "TRUE"
            .Cells(l, AudLastCol + 3).Value = "No Action required."
        Else
            .Cells(l, AudLastCol + 1).Value = "FALSE"
            .Cells(l, AudLastCol + 3).Value = "Band wise Carrier Mismatch."
        End If
    End If
Next l

I have tried this already. Still the answer comes out to be "true" instead of "False". Also, I have another else if in my code which I haven't mentioned above as it was working fine and that's why I hadn't used else in my code. — HobbyCoder, Apr 02 '18 at 16:28

DisplayName · Accepted Answer · 2018-04-03T05:20:25.827

0

Edited since OP’s code changed (...)

You have to change:

d19 = .Cells(l, Application.Match("Deployed(1.9)", .Range("A1:A" & AudLastCol), 0)).Value

to:

d19 = .Cells(l, Application.Match("Deployed(1.9)", .Rows(1).Resize(,AudLastCol), 0)).Value

to actually search in row1 to AudLastCol column instead of column 1 down to AudLastCol row

The same with other codelines

edited Apr 03 '18 at 05:20

answered Apr 02 '18 at 17:06

DisplayName

13,283
2
11
19

I have made some changes to the code above. The problem that I am facing is somewhat related to datatype in the above code, specially in the variable assignment but I don't know what to change and how. These d19, d8, etc. variables are always coming out to be empty. All my other elseif conditions(not mentioned above) are giving correct output. Please help. I am new to vba(P.S. Working on my 1st assignment.). – HobbyCoder Apr 03 '18 at 04:48
“I have made some changes to the code above”, did my code work before you made those changes? – DisplayName Apr 03 '18 at 05:05
No, The code is still not working and neither was earlier but I realized that I was using wrong logic with If statement. I am making some mistake for sure in the usage of match as I am getting an empty value in all the variables. I am using match here to find the column number of the cell in which a specific value is present in the 1st row. – HobbyCoder Apr 03 '18 at 05:19
See edited answer and, please, if this solves your _current_ question then mark it as accepted and don’t change your question code any more (post another question for another issue) – DisplayName Apr 03 '18 at 05:22
Thanks a lot. Now I know how to use Match function in a specific row. – HobbyCoder Apr 03 '18 at 05:43

Incorrect output with Nested IFs in vba

2 Answers2