Nested If/And Statements VBA

L

laxcat73

Board Regular
Joined
Aug 5, 2011
Messages
143
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:

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi because I will be away for some time, here's a piece of code that could get you started. Something along the lines of
Code: 
Dim bSwitch As Boolean
'Equivalent to If Range("P" & i).Value = "" And Range("Q" & i).Value = "" Then
If Range("P" & i).Value <> "" Or Range("Q" & i).Value <> "" Then
    bSwitch = True
Else
    bSwitch = False
End If
If Range("V" & i).Value <> "" Or Range("W" & i).Value <> "" And bSwitch = True Then
    bSwitch = True
Else
    bSwitch=False
End If
'.... Same throughout for other columns
 
If bSwitch Then MsgBox "there are values in all four pairs of columns specified"
 
End Sub
 
Upvote 0
Additional explanation:

The code will check one pair of columns at a time with a switch involved.
Provided that the switch is "on"/True, the code will continue to check the other columns while the switch is "on"/True. However, if it finds no values in a pair of columns, it will turn "off"/False the switch.
Then, the code will check if the switch is still turned "on"/True and pop up a message box since if the switch is turned on until the checking part, it means it has checked all the pairs of columns and have found values in them.
 
Upvote 0
kpark91 said:
Additional explanation:

The code will check one pair of columns at a time with a switch involved.
Provided that the switch is "on"/True, the code will continue to check the other columns while the switch is "on"/True. However, if it finds no values in a pair of columns, it will turn "off"/False the switch.
Then, the code will check if the switch is still turned "on"/True and pop up a message box since if the switch is turned on until the checking part, it means it has checked all the pairs of columns and have found values in them.
Click to expand...

Thanks! That's exactly what I wanted it to do.
 
Upvote 0
Well, I tried to mess around with that snippet you gave me and came up with this:

Code: 
[COLOR=blue]Dim[/COLOR] LR&, i&
            [COLOR=blue]LR[/COLOR] = Worksheets("Master").Range("F" & Rows.Count).End(xlUp).Row
            [COLOR=blue]For[/COLOR] i = 4 [COLOR=blue]To[/COLOR] LR
            [COLOR=blue]Dim[/COLOR] bSwitch [COLOR=blue]As[/COLOR] Boolean
            [COLOR=blue]If[/COLOR] Worksheets("Master").Range("P" & i).Value <> "" [COLOR=blue]Or[/COLOR] Worksheets("Master").Range("Q" & i).Value <> "" [COLOR=blue]Then[/COLOR]
                bSwitch = [COLOR=blue]True[/COLOR]
                Else
                bSwitch = [COLOR=blue]False[/COLOR]
            [COLOR=blue]End If
[/COLOR]                    [COLOR=blue]If[/COLOR] bSwitch = [COLOR=blue]False And[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=blue]Then[/COLOR]
                        Worksheets("Master").Range("P" & i).Value = Me.txtFirst.Value
                        Worksheets("Master").Range("Q" & i).Value = Me.txtPaid.Value
                    [COLOR=blue]End If
[/COLOR]            [COLOR=blue]If[/COLOR] Worksheets("Master").Range("V" & i).Value <> "" [COLOR=blue]Or[/COLOR] Worksheets("Master").Range("W" & i).Value <> "" [COLOR=blue]And[/COLOR] bSwitch = [COLOR=blue]True[/COLOR] [COLOR=blue]Then[/COLOR]
                bSwitch = [COLOR=blue]True[/COLOR]
                Else
                bSwitch = [COLOR=blue]False[/COLOR]
            [COLOR=blue]End If
[/COLOR]                    [COLOR=blue]If[/COLOR] bSwitch = [COLOR=blue]False And[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=blue]Then[/COLOR]
                        Worksheets("Master").Range("V" & i).Value = Me.txtFirst.Value
                        Worksheets("Master").Range("W" & i).Value = Me.txtPaid.Value
                    [COLOR=blue]End If
[/COLOR]            [COLOR=blue]If[/COLOR] Worksheets("Master").Range("AB" & i).Value <> "" Or Worksheets("Master").Range("AC" & i).Value <> "" [COLOR=blue]And[/COLOR] bSwitch = [COLOR=blue]True Then
[/COLOR]                bSwitch = [COLOR=blue]True[/COLOR]
                Else
                bSwitch = [COLOR=blue]False[/COLOR]
            [COLOR=blue]End If
[/COLOR]                    [COLOR=blue]If[/COLOR] bSwitch = [COLOR=blue]False And[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=blue]Then[/COLOR]
                        Worksheets("Master").Range("AB" & i).Value = Me.txtFirst.Value
                        Worksheets("Master").Range("AC" & i).Value = Me.txtPaid.Value
                    [COLOR=blue]End If
[/COLOR]            [COLOR=blue]If[/COLOR] Worksheets("Master").Range("AH" & i).Value <> "" [COLOR=blue]Or[/COLOR] Worksheets("Master").Range("AI" & i).Value <> "" [COLOR=blue]And[/COLOR] bSwitch = [COLOR=blue]True[/COLOR] [COLOR=blue]Then[/COLOR]
                bSwitch = [COLOR=blue]True[/COLOR]
                Else
                bSwitch = [COLOR=blue]False[/COLOR]
            [COLOR=blue]End If
[/COLOR]                    [COLOR=blue]If[/COLOR] bSwitch = [COLOR=blue]False And[/COLOR] Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value [COLOR=blue]Then[/COLOR]
                        Worksheets("Master").Range("AH" & i).Value = Me.txtFirst.Value
                        Worksheets("Master").Range("AI" & i).Value = Me.txtPaid.Value
                    [COLOR=blue]End If
[/COLOR]            [COLOR=blue]If[/COLOR] bSwitch = [COLOR=blue]True[/COLOR] [COLOR=blue]Then[/COLOR] MsgBox "Employee has completed all scheduled payments.  Please check for errors in previous dates."
                    Me.txtRef.SetFocus
            [COLOR=blue]Exit Sub
[/COLOR]        [COLOR=blue]Next[/COLOR] i

I really don't know enough to determine if this is a correct sequence and I don't know if I'm using all the commands correctly. This, as of now, gives no result whatsoever. The only part of that code that does anything is the Me.txtRef.SetFocus when I click "Create Entry" on my userform.
 
Upvote 0
Hi, laxcat.
I really can't tell what you're trying to do except that I know that you need to pop up a message box when there are values in all the pairs of columns you have specified.

I'm not too sure what you're trying to do with the If statements, so clarification is needed.
Meanwhile, I will shortly post an implemented 'msgbox' in your code without interfering with your If statements.
 
Upvote 0
Here is the code I have promised to post up.
Rich (BB code): 
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
    Dim LR&, i&, bSwitch As Boolean, WS As Worksheet
    bSwitch = False
    Set WS = Worksheets("Master")
    LR = Worksheets("Master").Range("F" & Rows.count).End(xlUp).Row
    For i = 4 To LR
        If WS.Range("P" & i).Value <> "" Or WS.Range("Q" & i).Value <> "" Then
            bSwitch = True
        Else
            bSwitch = False
        End If
        If bSwitch = True And WS.Range("V" & i).Value <> "" Or WS.Range("W" & i).Value <> "" Then
            bSwitch = True
        Else
            bSwitch = False
        End If
        If bSwitch = True And WS.Range("AB" & i).Value <> "" Or WS.Range("AC" & i).Value <> "" Then
            bSwitch = True
        Else
            bSwitch = False
        End If
        If bSwitch = True And WS.Range("AH" & i).Value <> "" Or WS.Range("AI" & i).Value <> "" Then
            bSwitch = True
        Else
            bSwitch = False
        End If
        
        If bSwitch Then MsgBox "Found all values"
        
        'Your code
        If Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value And _
            Worksheets("Master").Range("P" & i).Value = "" And _
            Worksheets("Master").Range("Q" & i).Value = "" Then
            Worksheets("Master").Range("P" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("Q" & i).Value = Me.txtPaid.Value
        ElseIf Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value And _
            Worksheets("Master").Range("V" & i).Value = "" And _
            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
        ElseIf Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value And _
            Worksheets("Master").Range("AB" & i).Value = "" And _
            Worksheets("Master").Range("AC" & i).Value = "" Then
            Worksheets("Master").Range("AB" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("AC" & i).Value = Me.txtPaid.Value
        ElseIf Worksheets("Master").Range("F" & i).Value = Me.txtRef.Value And _
            Worksheets("Master").Range("AH" & i).Value = "" And _
            Worksheets("Master").Range("AI" & i).Value = "" Then
            Worksheets("Master").Range("AH" & i).Value = Me.txtFirst.Value
            Worksheets("Master").Range("AI" & i).Value = Me.txtPaid.Value
        Else: MsgBox "Employee has completed all scheduled payments.  Please check for errors in previous dates."
            Me.txtFirst.SetFocus
           Exit Sub
        End If
        
    Next i
 
    Application.EnableEvents = True
 
    Me.txtRef.Value = ""
    Me.txtFirst.Value = ""
    Me.txtPaid.Value = ""
    Me.txtRef.SetFocus
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Code to record changes/logs modification needed?
Replies
0
Views
384
FGaxha
F
C
  • Solved
index match excel formula to vba code
Replies
9
Views
356
DanteAmor
DanteAmor
J
  • Solved
Speeding up VBA
Replies
9
Views
245
jvoss
J
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
267
MBarrett
M
J
  • Solved
LET/VSTACK Help
Replies
2
Views
179
Jedi Master
J

Forum statistics

Threads
1,224,545
Messages
6,179,432
Members
452,915
Latest member
hannnahheileen

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