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]

hmmmmmmm that's weird.
It works on mine.
It doesn't have any errors but doesn't populate the cells????
Try debugging it.

Where have you placed the code?

Meanwhile you can also try this.

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 = 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
            bSwitch = False
            WS.Range("P" & i).Value = Me.txtFirst.Value
            WS.Range("Q" & i).Value = Me.txtPaid.Value
        Else
            bSwitch = True
        End If
        If bSwitch = True And WS.Range("V" & i).Value = "" And WS.Range("W" & i).Value = "" Then
            bSwitch = False
            WS.Range("V" & i).Value = Me.txtFirst.Value
            WS.Range("W" & i).Value = Me.txtPaid.Value
        Else
            bSwitch = True
        End If
        If bSwitch = True And WS.Range("AB" & i).Value = "" And WS.Range("AC" & i).Value = "" Then
            bSwitch = False
            WS.Range("AB" & i).Value = Me.txtFirst.Value
            WS.Range("AC" & i).Value = Me.txtPaid.Value
        Else
            bSwitch = True
        End If
        If bSwitch = True And WS.Range("AH" & i).Value = "" And WS.Range("AI" & i).Value = "" Then
            bSwitch = False
            WS.Range("AH" & i).Value = Me.txtFirst.Value
            WS.Range("AI" & i).Value = Me.txtPaid.Value
        Else
            bSwitch = True
        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

 

[laxcat73]

Again, completes fine but it doesn't populate the cell.

This leads me to think that the reference cell is formatted incorrectly. Currently, the Reference # on the master sheet is formatted as a text. Would the code as looking it up as .Value rather than .Text matter?

 

[kpark91]

Yes... It does matter.
I told you to use .Text when I'm not even using it!! loll
Just change them to .Text

 

[laxcat73]

Yes... It does matter.
I told you to use .Text when I'm not even using it!! loll
Just change them to .Text

It's behaving very strange.. It works but it populates multiple columns. The first time I enter a date for one reference number, it populates column pairs 1 and 3 and then gives the error message...

I'm going to retry your code and see what happens

 

[laxcat73]

It's behaving very strange.. It works but it populates multiple columns. The first time I enter a date for one reference number, it populates column pairs 1 and 3 and then gives the error message...

I'm going to retry your code and see what happens

Ok. So this is what it does now:

First try entering a date, it populates column pairs 1 and 3, then displays the MsgBox. Second try, without deleting the first try's output, it populates column pairs 2 and 4 and does NOT prompt MsgBox.

I really have no clue at this point. It seems like all 4 if statements are running simultaneously and we need to nest them sequentially

 

[kpark91]

Ok. So this is what it does now:

First try entering a date, it populates column pairs 1 and 3, then displays the MsgBox. Second try, without deleting the first try's output, it populates column pairs 2 and 4 and does NOT prompt MsgBox.

I really have no clue at this point. It seems like all 4 if statements are running simultaneously and we need to nest them sequentially

At least it populates now xD
Anyways, that's because if P + Q have data in them it doesn't bother checking other columns

 

[laxcat73]

At least it populates now xD
Anyways, that's because if P + Q have data in them it doesn't bother checking other columns

Yeah I know but on first enter of data, it populates P and AB. And it gives me the error message.

 

[kpark91]

Do you have multiple same-named reference numbers?

 

[laxcat73]

Do you have multiple same-named reference numbers?

Reference number is unique to each row. Here's a screenshot of what it does.

Note: The cell that says ERROR-1 is an in cell formula to make it easier to spot these issues.


http://imageshack.us

 

[kpark91]

It'd be great if you could send me your workbook.
Any sensitive data, you can just write
=RAND()

and then copy down or right or w.e!!

I'll PM u my email because I really don't think I know what the problem is until I see it.

PS: Any other takers? I won't be able to answer it by tomorrow or even Sunday.