Nested If/And Statements VBA

[laxcat73]

Hey so I am having difficulty with this being very new at VBA. My objective is to use the data from the userform to populate cells determined by one of the userform entries. However, I want it to be a conditional entry. If there is a value in either one of 2 columns for 1 date, I want to have it move to the next column and check to see if they have any. Loop it to run in 4 separate pairs of columns sequentially (P+Q, V+W, AB+AC, AH+AI) and if there is a value in all of the 4 pairs, display a msgbox.

Here is my code which currently does NOT work:

[COLOR=royalblue]Private Sub[/COLOR] cmdCreate_Click()
[COLOR=royalblue]If[/COLOR] Trim(Me.txtRef.Value) = "" [COLOR=royalblue]Then[/COLOR]
  Me.txtRef.SetFocus
  MsgBox "Please enter a reference #"
 [COLOR=royalblue] Exit Sub[/COLOR]
[COLOR=royalblue]End If[/COLOR]
    
    Application.EnableEvents = [COLOR=royalblue]False[/COLOR]
    [COLOR=royalblue]Dim[/COLOR] LR&, i&
    LR = Worksheets("Master").Range("F" & Rows.Count).End(xlUp).Row
    [COLOR=royalblue]For[/COLOR] i = 4 [COLOR=royalblue]To[/COLOR] LR
        [COLOR=royalblue]If[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("P" & i).Value = "" [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("Q" & i).Value = "" [COLOR=royalblue]Then[/COLOR]
            Worksheets("Master").Range("P" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("Q" & i).Value = Me.txtPaid.Value
        [COLOR=royalblue]ElseIf[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("V" & i).Value = "" [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("W" & i).Value = "" Then
            Worksheets("Master").Range("V" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("W" & i).Value = Me.txtPaid.Value
        [COLOR=royalblue]ElseIf[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("AB" & i).Value = "" [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("AC" & i).Value = "" [COLOR=royalblue]Then[/COLOR]
            Worksheets("Master").Range("AB" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("AC" & i).Value = Me.txtPaid.Value
        [COLOR=royalblue]ElseIf[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("AH" & i).Value = "" [COLOR=royalblue]And[/COLOR] _
            Worksheets("Master").Range("AI" & i).Value = "" [COLOR=royalblue]Then[/COLOR]
            Worksheets("Master").Range("AH" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("AI" & i).Value = Me.txtPaid.Value
        [COLOR=royalblue]Else[/COLOR]: MsgBox "Employee has completed all scheduled payments.  Please check for errors in previous dates."
            Me.txtFirst.SetFocus
 
           [COLOR=royalblue]Exit Sub[/COLOR]
[COLOR=royalblue]           End If[/COLOR]
[COLOR=royalblue]           Next[/COLOR] i
 
    Application.EnableEvents = [COLOR=royalblue]True[/COLOR]
 
Me.txtRef.Value = ""
Me.txtFirst.Value = ""
Me.txtPaid.Value = ""
Me.txtRef.SetFocus
[COLOR=royalblue]End Sub[/COLOR]

 

[kpark91]

Hi, i'm taking a look at your spreadsheet right now and there are alot of modules (same ones).
Which one should I edit?

 

[kpark91]

I see 4 forms that do the same thing but to different columns.
Do you essentially want to combine those 4 forms to allow easier inputs???

 

[kpark91]

It seems I have misunderstood the problem in the beginning because of the word 'sequentially.'
I automatically thought that it would not check other columns if it already found a place to enter.

Here is the code that you can input into textboxes once and fills all the 1st/2nd/3rd adnd 4th installment dates and reasons.

Private Sub cmdCreate_Click()
    If Trim(Me.txtRef.Value) = "" Then
        Me.txtRef.SetFocus
        MsgBox "Please enter a reference #"
        Exit Sub
    End If
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    Dim LR&, i&, bSw1 As Byte, bSw2 As Byte, bSw3 As Byte, bSw4 As Byte, WS As Worksheet
    Set WS = Worksheets("Master")
    bSw1 = False
    bSw2 = False
    bSw3 = False
    bSw4 = False
    
    LR = WS.Range("F" & Rows.Count).End(xlUp).Row
    For i = 4 To LR
       If WS.Range("F" & i).Value = Me.txtRef.Value Then
        If WS.Range("P" & i).Value = "" And WS.Range("Q" & i).Value = "" Then
            bSw1 = False
            WS.Range("P" & i).Value = Me.txtFirst.Value
            WS.Range("Q" & i).Value = Me.txtPaid.Value
        Else
            bSw1 = True
        End If
        
        If WS.Range("V" & i).Value = "" And WS.Range("W" & i).Value = "" Then
            bSw2 = False
            WS.Range("V" & i).Value = Me.txtFirst.Value
            WS.Range("W" & i).Value = Me.txtPaid.Value
        Else
            bSw2 = True
        End If
        If WS.Range("AB" & i).Value = "" And WS.Range("AC" & i).Value = "" Then
            bSw3 = False
            WS.Range("AB" & i).Value = Me.txtFirst.Value
            WS.Range("AC" & i).Value = Me.txtPaid.Value
        Else
            bSw3 = True
        End If
        If WS.Range("AH" & i).Value = "" And WS.Range("AI" & i).Value = "" Then
            bSw4 = False
            WS.Range("AH" & i).Value = Me.txtFirst.Value
            WS.Range("AI" & i).Value = Me.txtPaid.Value
        Else
            bSw4 = True
        End If
 
        If bSw1 And bSw2 And bSw3 And bSw4 Then
            MsgBox "Employee has completed all scheduled payments.  Please check for errors in previous dates."
            Me.txtFirst.SetFocus
            Exit Sub
        End If
       End If
    Next i
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
    Me.txtRef.Value = ""
    Me.txtFirst.Value = ""
    Me.txtPaid.Value = ""
    Me.txtRef.SetFocus
 
End Sub
Private Sub cmdCancel_Click()
  Unload Me
End Sub

 

[laxcat73]

I see 4 forms that do the same thing but to different columns.
Do you essentially want to combine those 4 forms to allow easier inputs???

Yes. Before we had it so you would have to know which column you were entering into, and use the corresponding user form. Now, I wanted to simplify it to 1 userform that would simply put the Date/reason in the first empty set of columns, chronologically

 

[kpark91]

Yes. Before we had it so you would have to know which column you were entering into, and use the corresponding user form. Now, I wanted to simplify it to 1 userform that would simply put the Date/reason in the first empty set of columns, chronologically

Oh... Then don't use the code I've posted lol.
It doesn't look for the first empty set of columns.

 

[laxcat73]

Oh... Then don't use the code I've posted lol.
It doesn't look for the first empty set of columns.

Lol.. I think that I figured that out by the fact it didn't work and I was having issues with it.

 

[kpark91]

Ugh. My bad.
It was me who messed up from the very fundamentals lol.
My logic was totally wrong;;

Private Sub cmdCreate_Click()
    If Trim(Me.txtRef.Value) = "" Then
        Me.txtRef.SetFocus
        MsgBox "Please enter a reference #"
        Exit Sub
    End If
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    Dim LR&, i&, bSwitch As Boolean, WS As Worksheet
    Set WS = Worksheets("Master")
    bSwitch = True
    bMsgBox = False
    LR = WS.Range("F" & Rows.Count).End(xlUp).Row
    For i = 4 To LR
        If WS.Range("F" & i).Value = Trim$(Me.txtRef.Value) Then
            
            If Trim$(WS.Range("P" & i).Value) = "" And Trim$(WS.Range("Q" & i).Value) = "" Then
                WS.Range("P" & i).Value = Me.txtFirst.Value
                WS.Range("Q" & i).Value = Me.txtPaid.Value
                bSwitch = False
            End If
            
            If bSwitch And Trim$(WS.Range("V" & i).Value) = "" And Trim$(WS.Range("W" & i).Value) = "" Then
                WS.Range("V" & i).Value = Me.txtFirst.Value
                WS.Range("W" & i).Value = Me.txtPaid.Value
                bSwitch = False
            End If
            
            If bSwitch And Trim$(WS.Range("AB" & i).Value) = "" And Trim$(WS.Range("AC" & i).Value) = "" Then
                WS.Range("AB" & i).Value = Me.txtFirst.Value
                WS.Range("AC" & i).Value = Me.txtPaid.Value
                bSwitch = False
            End If
            
            If bSwitch And Trim$(WS.Range("AH" & i).Value) = "" And Trim$(WS.Range("AI" & i).Value) = "" Then
                WS.Range("AH" & i).Value = Me.txtFirst.Value
                WS.Range("AI" & i).Value = Me.txtPaid.Value
                bSwitch = False
            End If
            
            If bSwitch Then
                MsgBox "Employee has completed all scheduled payments.  Please check for errors in previous dates."
                Me.txtFirst.SetFocus
                Exit Sub
            End If
       End If
    Next i
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
    Me.txtRef.Value = ""
    Me.txtFirst.Value = ""
    Me.txtPaid.Value = ""
    Me.txtRef.SetFocus
 
End Sub
Private Sub cmdCancel_Click()
  Unload Me
End Sub

worked on the spreadsheet you gave me. So, let's hope it works on yours too!

 

[laxcat73]

Works!

You're the best