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]
 
Sort by date Sort by votes
kpark, it is a continuation of the code you helped me with several days ago. I want to combine the code you gave me here:
http://www.mrexcel.com/forum/showpost.php?p=2821102&postcount=24

into a string of code that runs that 4 times, for each pair of columns shown above. In addition, I want that to have an error message if all 4 pairs of columns have a value in them. I tried to see if I could do this on my own but I obviously don't know enough about VBA to do this solo. I can easily figure stuff out but this is hard since I have no reference.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Still! Big improvement in your VBA skills from last time laxcat!
Copying + pasting code/ typing up code from other people is a start.


Anyways, it'd be great if you can verify what I think you're trying to do...

You want to check if the column F has the same value as the user input (reference number) and if it does, you want to fill them into missing data of the columns you have specified.

So for example, if either one of P + Q are empty in the matching reference number, you want to fill them in with the correct user inputs.
Do you also want V + W to have the same data as P + Q because what your code did was stop copying data once data has been filled into one of the four pairs of columns.
 
Upvote 0
kpark91 said:
Still! Big improvement in your VBA skills from last time laxcat!
Copying + pasting code/ typing up code from other people is a start.


Anyways, it'd be great if you can verify what I think you're trying to do...

You want to check if the column F has the same value as the user input (reference number) and if it does, you want to fill them into missing data of the columns you have specified.

So for example, if either one of P + Q are empty in the matching reference number, you want to fill them in with the correct user inputs.
Do you also want V + W to have the same data as P + Q because what your code did was stop copying data once data has been filled into one of the four pairs of columns.
Click to expand...

You got the jist of what I am trying to do. The only correction I need to make in your assessment of what I want is this. Replace either one of with BOTH

kpark91 said:
So for example, if either one of P + Q are empty in the matching reference number, you want to fill them in with the correct user inputs.
Click to expand...

and this:

kpark91 said:
Do you also want V + W to have the same data as P + Q because what your code did was stop copying data once data has been filled into one of the four pairs of columns.
Click to expand...

I want the macro to skip P+Q if EITHER has a value, and move the V+W and check those. I want it to input only if NEITHER has a value, otherwise loop the logic to the next pair AB+AC and then to AH+AI. If all of the said pairs have a vlaue in EITHER column, I want the MsgBox to prompt and say that the user needs to check for errors.

Thank you again for your help!
 
Upvote 0
Try this code:
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
    Set WS = Worksheets("Master")
    bSwitch = False
    LR = WS.Range("F" & Rows.count).End(xlUp).Row
    For i = 4 To LR
        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
    Next i
 
    Application.EnableEvents = True
 
    Me.txtRef.Value = ""
    Me.txtFirst.Value = ""
    Me.txtPaid.Value = ""
    Me.txtRef.SetFocus
End Sub
 
Upvote 0
Ok, so the code for whatever reason isn't working by finding the empty cells or it just jumps straight to the MsgBox command.




Here are screenshots. Row 39 is a sample to test the macro - ignore it's contents other than reference number.



http://imageshack.us
 
Last edited:
Upvote 0
Forgot the most important part...
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
    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
 
    Me.txtRef.Value = ""
    Me.txtFirst.Value = ""
    Me.txtPaid.Value = ""
    Me.txtRef.SetFocus
End Sub
 
Upvote 0
It still isn't working correctly. Now the process completes successfully but there is no data outpout. It doesn't populate the cell with anything. Who knew coding is such a pain :(
 
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,550
Messages
6,179,462
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