If Then Question

[wazzulu1]

Hi;

I am tasked to replicate this module I have posted in another application, and have run into some issues in doing so. Forgive my need to ask an obvious question to some who are more versed in VBA, but in examining the posted module below, I am trying to verify that I am capturing the right concept in what has been coded. (References to Column 26 has been the problem for me)

Example: This is in the actual code below:

If wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21) Then
            If wsSheet.Cells(ir, 24) = "POL" Then
                If wsSheet.Cells(ir, 18) = "Denied" Then
                    wsSheet.Cells(ir, 26) = "DEL"

Am I interpreting this correctly as depicting this in this fashion?
If [ir,22] = [ir,21] and [ir,24] = "POL and [ir,18] = "Denied", Then (make) [ir,26](to equal) "DEL"

So far, I am not matching the results, which is making me question if I am misinterpreting these If Then statements.

Any insight is greatly appreciated!

Public Sub GetAMyHrEmp()    Dim stemp As String
    Dim cnstr As String
    Dim ir As Integer
    Dim EEId As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim JobEffDate As String
    Dim FullNameMYHr As String
    Dim EmpNameAetna As String
    Dim dateE As Date
    Dim dateQ As Date
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    On Error GoTo ErrChk
    Application.DisplayAlerts = False


    cnstr = CSVFolder & "\" & CurrentMyHrDaily
    Debug.Print cnstr


    cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & cnstr
  
    On Error GoTo ErrChk
    If cn.State <> adStateOpen Then Exit Sub
    
    Set rs = New ADODB.Recordset
    On Error Resume Next
    
    
    EEId = ""
    ir = 2
     
     Do Until ir > 10000
        If wsSheet.Cells(ir, 2) = "" Then
            Exit Do
        End If
        EEId = wsSheet.Cells(ir, 2)    'emp id
        EmpNameAetna = wsSheet.Cells(ir, 1)
        
        strSQL = "SELECT [Full Name] , [Job Effective date], [Action Reason] FROM [Sheet1$] where [Employee ID] = '" & EEId & "'"
'        Debug.Print strSQL
    
        rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
        On Error GoTo ErrChk
        If rs.State <> adStateOpen Then
            cn.Close
            Set rn = Nothing
            Set cn = Nothing
            Exit Sub
        End If
  
             wsSheet.Cells(1, 20) = "MyHr Leave date"
             wsSheet.Cells(1, 21) = "Earliest Start"
             wsSheet.Cells(1, 22) = "Report Out"
             wsSheet.Cells(1, 23) = "Check RTW"
             wsSheet.Cells(1, 24) = "Leave Type"
            wsSheet.Cells(1, 25) = "MyHr Type"
            wsSheet.Cells(1, 26) = "Notes"
               wsSheet.Cells(ir, 26).Select
                Selection.Font.ColorIndex = 1   'black
              
             wsSheet.Cells(ir, 20) = ""
  '          wsSheet.Cells(ir, 26) = "not in MyHr"
       Do Until rs.EOF
            JobEffDate = rs.Fields.Item("Job Effective date")
       '     If Bank = 9000 Then Bank = 1
            FullNameMYHr = rs.Fields.Item("Full Name")
            wsSheet.Cells(ir, 20) = JobEffDate
           wsSheet.Cells(ir, 25) = rs.Fields.Item("Action Reason")
    '         wsSheet.Cells(ir, 26) = FullNameMYHr
             rs.MoveNext
        Loop
    
        rs.Close
         
        'set col U to earliest of E 'leave Start date' or Q 'std date of dis"
         dateE = wsSheet.Cells(ir, 5)
         dateQ = wsSheet.Cells(ir, 17)      'std
         If Trim(wsSheet.Cells(ir, 17)) <> "" Then
            If dateE < dateQ Then
               wsSheet.Cells(ir, 21) = dateE
            Else
              wsSheet.Cells(ir, 21) = dateQ
            End If
         Else
            wsSheet.Cells(ir, 21) = dateE
         End If
          
         'set col V report out
          If wsSheet.Cells(ir, 20) = "" Then
            wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21)
             wsSheet.Cells(ir, 26) = "New"
         End If
          If wsSheet.Cells(ir, 20) = wsSheet.Cells(ir, 21) Then
            wsSheet.Cells(ir, 22) = "DEL"
          Else
            wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21)
          End If
          
          'set col W actual return to work
           If wsSheet.Cells(ir, 12) = "" Then  ' L = arw
            wsSheet.Cells(ir, 23) = "OK"
           Else
            wsSheet.Cells(ir, 23) = wsSheet.Cells(ir, 12)
         End If
         
         'set col X to MyHr leave type code
         ' D = 4 = Leave type
         ' I = 9 = Leave Status
         If Trim(wsSheet.Cells(ir, 4)) = "Continuous  Leave" Then
            If wsSheet.Cells(ir, 9) = "Approved" Then
                wsSheet.Cells(ir, 24) = "STR"
            End If
           If wsSheet.Cells(ir, 9) = "Pended" Then
                wsSheet.Cells(ir, 24) = "STR"
            End If
         End If
         
         If wsSheet.Cells(ir, 4) = "TDMAL" Then
            If wsSheet.Cells(ir, 9) = "Approved" Then
                wsSheet.Cells(ir, 24) = "MAL"
            End If
           If wsSheet.Cells(ir, 9) = "Pended" Then
                wsSheet.Cells(ir, 24) = "MAL"
            End If
         End If


         If wsSheet.Cells(ir, 4) = "TDML" Then
            If wsSheet.Cells(ir, 9) = "Approved" Then
                wsSheet.Cells(ir, 24) = "MIL"
            End If
           If wsSheet.Cells(ir, 9) = "Pended" Then
                wsSheet.Cells(ir, 24) = "MIL"
            End If
         End If
 
         
         If wsSheet.Cells(ir, 4) = "TDPL" Then
            If wsSheet.Cells(ir, 9) = "Approved" Then
                wsSheet.Cells(ir, 24) = "PER"
            End If
           If wsSheet.Cells(ir, 9) = "Pended" Then
                wsSheet.Cells(ir, 24) = "PER"
            End If
         
         End If
         If wsSheet.Cells(ir, 9) = "Denied" Then
            wsSheet.Cells(ir, 24) = "POL"
'fix 4/23/2013
                wsSheet.Cells(ir, 26) = Trim(wsSheet.Cells(ir, 26)) & " CHECK if EE at Work "
                wsSheet.Cells(ir, 26).Select
                Selection.Font.ColorIndex = 3   'red
'fix 4/23/2013
         End If
        
        
        If wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21) Then
            If wsSheet.Cells(ir, 24) = "POL" Then
                If wsSheet.Cells(ir, 18) = "Denied" Then
                    wsSheet.Cells(ir, 26) = "DEL"
                 
                End If
                
            End If
        End If
        
        If wsSheet.Cells(ir, 22) = "DEL" Then
            If wsSheet.Cells(ir, 24) <> wsSheet.Cells(ir, 25) Then
                wsSheet.Cells(ir, 26) = Trim(wsSheet.Cells(ir, 26)) & " Update leave type to " & wsSheet.Cells(ir, 24)
            Else
                wsSheet.Cells(ir, 26) = wsSheet.Cells(ir, 9)
            End If
        Else
            If Not (wsSheet.Cells(ir, 20) = "") Then
                wsSheet.Cells(ir, 26) = Trim(wsSheet.Cells(ir, 26) & " Check dates ")
                wsSheet.Cells(ir, 26).Select
                Selection.Font.ColorIndex = 3   'red
                If wsSheet.Cells(ir, 24) <> wsSheet.Cells(ir, 25) Then
                    wsSheet.Cells(ir, 26) = Trim(wsSheet.Cells(ir, 26) & " Update leave type to " & wsSheet.Cells(ir, 24))
                End If
            Else
                wsSheet.Cells(ir, 26) = Trim(wsSheet.Cells(ir, 26) & " " & wsSheet.Cells(ir, 24))
            End If
        End If
        
          If wsSheet.Cells(ir, 23) <> "OK" Then
            If (wsSheet.Cells(ir, 20) = "") Then
                wsSheet.Cells(ir, 26) = wsSheet.Cells(ir, 26) & " check if already RTW " & wsSheet.Cells(ir, 23)
            Else
                wsSheet.Cells(ir, 26) = wsSheet.Cells(ir, 26) & " check RTW " & wsSheet.Cells(ir, 23)
            End If
            wsSheet.Cells(ir, 26).Select
            Selection.Font.ColorIndex = 3   'red
         End If
       
         If Trim(wsSheet.Cells(ir, 17)) <> "" Then
 '       Debug.Print DateDiff("d", dateQ, dateE)
            If Abs(DateDiff("d", dateQ, dateE)) > 4 Then
               wsSheet.Cells(ir, 26) = wsSheet.Cells(ir, 26) & " check LOA and STD dates"
               wsSheet.Cells(ir, 26).Select
               Selection.Font.ColorIndex = 3   'red
            End If
         End If
        
        If InStr(1, wsSheet.Cells(ir, 26), "Denied") > 0 Then
               wsSheet.Cells(ir, 26) = wsSheet.Cells(ir, 26) & " Check if needed"
         End If




        ir = ir + 1
    Loop
    
    Range("A2").Select


       cn.Close
    Set rs = Nothing
    Set cn = Nothing
 '   oWB3.Close
    
    
    Application.DisplayAlerts = True
    
    Application.Cursor = xlDefault


Exit_ErrChk:
    Exit Sub


ErrChk:
    MsgBox Trim(EmpId) & ": ERROR GetAMyHrEmp" & Err.Description
 '   oWB3.Close
    Application.Cursor = xlDefault
    Application.DisplayAlerts = True
'    End
    Resume Exit_ErrChk


End Sub

 

[shg]

That's not valid syntax.

 

[wazzulu1]

Isn't that what this is below?

If wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21) Then
If wsSheet.Cells(ir, 24) = "POL" Then
If wsSheet.Cells(ir, 18) = "Denied" Then
wsSheet.Cells(ir, 26) = "DEL"

 

[shg]

No, it's three nested If/Then statements

  If wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21) Then
    If wsSheet.Cells(ir, 24) = "POL" Then
      If wsSheet.Cells(ir, 18) = "Denied" Then
        wsSheet.Cells(ir, 26) = "DEL"
      End If
    End If
  End If

The statements inside a block If statement can be anything, including other block Ifs (or anything else).

 

[wazzulu1]

So this was my initial question: this was not a statement to combine all three conditions together, they are three separate conditions that if any one of them are met, then the value should be "DEL"?

so really, this should be like this:

If [ir, 22] = [ir, 21], then "DEL"

or

If [ir 24] = "POL", then "DEL"

or

If [ir, 18] = "Denied", then "DEL"

Is this correct?

 

[shg]

No. If they were combined into a single statement, it would look like this:

  If wsSheet.Cells(ir, 22) = wsSheet.Cells(ir, 21) And _
     wsSheet.Cells(ir, 24) = "POL" And _
     wsSheet.Cells(ir, 18) = "Denied" Then
    wsSheet.Cells(ir, 26) = "DEL"
  End If

 

[wazzulu1]

Ok;

I apologize for all these questions, so this does confirm that all these conditions are to be met to change the value of the field. This is what I needed to confirm.

 

[shg]

No worries.

There's good reason not to combine the statements in that fashion; it's slower, because all conditions need to be evaluated.

 

[wazzulu1]

Thank You for all of your feedback, this has helped me tremendously.

 

[shg]

You're welcome.

I'd urge you to spend some quality time reading Debugging VBA

 

[wazzulu1]

Thank You;

I will read this. I am unable to execute the code due to not having all of the input files, so I am at the mercy of the folks supplying me with the before and after files for evaluation.