If Then Question

W

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
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:
Code: 
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!

Code: 
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Your analysis looks correct; have you tried stepping through the code?

You're aware that text comparisons in VBA are case-sensitive by default?
 
Upvote 0
Hi;

Yes, I am aware of that. The program is said to be producing results that I could not verify yet, because the owners sent me a file that they manually updated after they generated the initial output file. So I am waiting until next week for them to send me a untouched file, so I can compare it against my new programming efforts. The way this module was written, I wanted to verify that I was interpreting it correctly, so I can build the same logic outside of Excel. Thanks for the verification. Is it correct to assume that the last statement of any given If Then Statement should be the expected outcome/value in each one? This would mean to me that anything before that would either be a continuous "AND" or "OR" statement for the logical comparisons.
 
Upvote 0
I can't parse your question. The syntax of an if/then/else statement (copied from Help) is

Code: 
If condition Then [statements] [Else elsestatements]

or

Code: 
If condition Then
  [statements]
[ElseIf condition-n Then
  [elseifstatements] 
  ... 
[Else
  [elsestatements]]
End If
 
Upvote 0
I think one of the big questions I had was that an "Else If" was not us, but continuous "Then", which amounts to the same thing as the example you gave, correct?
 
Upvote 0
[I can't parse your question.] Not sure what I missed with this? Was my question too wordy in trying to ask a question?
 
Upvote 0
If/Then statements can be nested as deeply as you like. They are the same at every level.

Code: 
  If a Then     ' first level
    If b Then   ' second level
      If c Then ' third level
        x = 1
      ElseIf d Then
        x = 2
      End If
    End If
  End If
 
Last edited:
Upvote 0
So either an "Else If" or a continuous usage of "Then" would be equivalent? Other that perhaps greater clarity, is there any benefit to using one or the other?

Someone else wrote this (they are no longer available), so this is why I have gone through each piece to understand the step progression.
 
Upvote 0
I don't know what you mean by "a continuous use of Then"
 
Upvote 0
Meaning;

Instead of of "If", "Then", "Else If","Else";

The former programmer choose to write: "If","Then",Then","Then"
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
298
alessiaexcels
A
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
300
Alex Blakenburg
A
S
  • Question Question
Macro to change one cell on a sheet, save sheet as PDF, then loop back and change that one cell on that sheet and repeat until the end
Replies
3
Views
703
IslandofBDA
I
Sandeep Warrier
  • Question Question
Calculate No. of Working Days based on Shift Pattern
Replies
2
Views
159
Tetra201
Tetra201
G
  • Solved
Run-Time Error 381
2
Replies
12
Views
216
Micron
M

Forum statistics

Threads
1,224,931
Messages
6,181,838
Members
453,067
Latest member
mdiz777

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top