If then VBA

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
I am having difficulty creating the code that if a certain condition is not met, it cycles to the next. The code is orange works great, but I need it to do the following afterwards if the next condition is not met:

IF D26 = "DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER.", then a message box pops up saying, ""DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER. NOTHING IS REQUIRED OF YOU FOR THIS ITEM."

If that condition is not met and D26 = "SEND TO OFFICE TO CREATE A BACKORDER.", then a message box pops up saying, "THE RULES FOR THIS CUSTOMER REQUIRE THE FRONT OFFICE TO CREATE A BACKORDER. DO NOT ENTER A SPECIAL BATCH. THE FRONT OFFICE HAS BEEN NOTIFIED, AND NOTHING FURTHER IS REQUIRED." Also, if this condition is met, I would like it to send an email to someone saying, "A BACKORDER IS REQUIRED FOR [input worskeet cell G4], QTY [input worskeet cell M7], PO NUMBER [input worskeet cell G20].

Once all of these are done, I would like the MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX." to pop up.

I currently have the following:

Sub SENDTOLOG_Click()
Application.ScreenUpdating = False
Dim CS As Worksheet, PS As Worksheet, lr As Long, M As Long

Set CS = Worksheets("Input")
Set PS = Worksheets("Records")

If Range("G4") = "" Then
MsgBox "Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G7") = "" Then
MsgBox "Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M7") = "" Then
MsgBox "Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G14") = "" Then
MsgBox "Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("M14") = "" Then
MsgBox "Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Range("G20") = "" Then
MsgBox "Please enter the PO NUMBER" & vbCrLf & " " & vbCrLf & "XXXXXXX"
ElseIf Worksheets("Input").Range("D26").Value = "CREATE SPECIAL BATCH" Then
MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
sText = Application.InputBox("ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:")

MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
'Sheets("Input").Activate
'Application.ScreenUpdating = True

'Find First Empty Row after Data
lr = PS.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Copy data from the INPUT to the RECORDS worksheet

'copy CUSTOMER #
PS.Cells(lr, 1).Value = CS.Range("M4").Value
'copy CUSTOMER NAME
PS.Cells(lr, 2).Value = CS.Range("G4").Value
'copy ENTRY DATE/TIME
PS.Cells(lr, 3).Value = CS.Range("D17").Value
'copy TICKET LOAD DATE
PS.Cells(lr, 4).Value = CS.Range("G14").Value
'copy LINE QTY
PS.Cells(lr, 5).Value = CS.Range("G7").Value
'copy QTY REJECTED
PS.Cells(lr, 6).Value = CS.Range("M7").Value
'copy % REJECTED
PS.Cells(lr, 7).Value = CS.Range("P7").Value
'copy RULE
PS.Cells(lr, 8).Value = CS.Range("G11").Value
'copy RESPONSE
PS.Cells(lr, 9).Value = CS.Range("D26").Value
'copy rejected by:
PS.Cells(lr, 10).Value = CS.Range("M14").Value
'copy before 2pm:
PS.Cells(lr, 11).Value = CS.Range("S24").Value
'copy load date after today?:
PS.Cells(lr, 12).Value = CS.Range("T24").Value
'copy PO start w/ number?:
PS.Cells(lr, 13).Value = CS.Range("U24").Value
'copy less than 10 rejected?:
PS.Cells(lr, 14).Value = CS.Range("V24").Value
'copy 5 or less rejected?:
PS.Cells(lr, 15).Value = CS.Range("X24").Value
'copy less than % rule?:
PS.Cells(lr, 16).Value = CS.Range("Y24").Value

End Sub
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Where is your closure of the 'If' statements?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Well, in the mean time, I did a quick clean up of some of the code.

VBA Code:
Sub SENDTOLOG_Click()
'
    Application.ScreenUpdating = False
'
    Dim CS As Worksheet, PS As Worksheet, lr As Long        ' , M As Long
'
    Set CS = Worksheets("Input")
    Set PS = Worksheets("Records")
'
    If Range("G4") = "" Then
        MsgBox "Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Range("G7") = "" Then
        MsgBox "Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Range("M7") = "" Then
        MsgBox "Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Range("G14") = "" Then
        MsgBox "Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Range("M14") = "" Then
        MsgBox "Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Range("G20") = "" Then
        MsgBox "Please enter the PO NUMBER" & vbCrLf & " " & vbCrLf & "XXXXXXX"
    ElseIf Worksheets("Input").Range("D26").Value = "CREATE SPECIAL BATCH" Then
        MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
        sText = Application.InputBox("ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:")
'
        MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
'
'
'
''    End If
'
        lr = PS.Cells(Rows.Count, 1).End(xlUp).Row + 1                  ' Find First Empty Row after Data
'
'       Copy data from the INPUT to the RECORDS worksheet
        PS.Cells(lr, 1).Value = CS.Range("M4").Value                    ' copy CUSTOMER #
        PS.Cells(lr, 2).Value = CS.Range("G4").Value                    ' copy CUSTOMER NAME
        PS.Cells(lr, 3).Value = CS.Range("D17").Value                   ' copy ENTRY DATE/TIME
        PS.Cells(lr, 4).Value = CS.Range("G14").Value                   ' copy TICKET LOAD DATE
        PS.Cells(lr, 5).Value = CS.Range("G7").Value                    ' copy LINE QTY
        PS.Cells(lr, 6).Value = CS.Range("M7").Value                    ' copy QTY REJECTED
        PS.Cells(lr, 7).Value = CS.Range("P7").Value                    ' copy % REJECTED
        PS.Cells(lr, 8).Value = CS.Range("G11").Value                   ' copy RULE
        PS.Cells(lr, 9).Value = CS.Range("D26").Value                   ' copy RESPONSE
        PS.Cells(lr, 10).Value = CS.Range("M14").Value                  ' copy rejected by:
'
        PS.Cells(lr, 11).Resize(, 4).Value = CS.Range("S24").Resize(, 4).Value
'
        PS.Cells(lr, 15).Resize(, 2).Value = CS.Range("X24").Resize(, 2).Value
'
        Application.ScreenUpdating = True                               ' Should turn this back on ;)
End Sub
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
Thank you for the help! I'm still learning....

I'm having trouble with this portion specifically right now. I want it to read through the blue first, then send the "MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."

If the blue isn't true, move to the orange, then send the "MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."

Then move to the green. If green, I want it to send an email to someone saying, "A backorder is required for (input tab/cell G4), Qty: (input tab/cell M4), PO# (input tab/cell G20)", then MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."

Can you please help?

ElseIf Worksheets("Input").Range("D26").Value = "CREATE SPECIAL BATCH" Then
MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
sText = Application.InputBox("ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:")
Sheets("Input").Activate
Application.ScreenUpdating = True
With Sheets("Records")
lr = .Range("A" & Rows.Count).End(3).Row + 1
'.Range("A" & lr).Value = Now
.Range("Q" & lr).Value = sText
'.Range("C" & lr).Value = sName
If sText = "" Or sText = False Then

ElseIf Worksheets("Input").Range("D26").Value = "DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER." Then
MsgBox ("DO NOT CREATE A SPECIAL BATCH AND DO NOT BACKORDER. NOTHING IS REQUIRED OF YOU FOR THIS ITEM.")

ElseIf Worksheets("Input").Range("D26").Value = "SEND TO OFFICE TO CREATE A BACKORDER." Then
'MsgBox ("THE RULES FOR THIS CUSTOMER REQUIRE THE FRONT OFFICE TO CREATE A BACKORDER. DO NOT ENTER A SPECIAL BATCH. THE FRONT OFFICE HAS BEEN NOTIFIED, AND NOTHING FURTHER IS REQUIRED.")

'
MsgBox "THE RECORD HAS BEEN SAVED." & vbCrLf & " " & vbCrLf & "XXXXXXX."
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Are you sure you want all of those ElseIf's in your original post?

The way you have the code, if G4 = "" it will pop up a message box indicating the name needs to be entered and then it will skip all of those other ElseIf statements that check for = ""
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
What I am trying to do there is that if they dont enter data in one of those cells, I dont want the code to move forward. I want a message to pop up letting them know data is incomplete. This appears to be working for me right now.

I'm struggling with how to loop through what I posted above. Appreciate the help!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

What I am saying is the ElseIf's are not going to be executed after one of the checks for = "" is true.

Are you wanting to check all of those addresses G4,G7,M7,G14,M14,G20 for ="" and have them filled in if they are = ""?
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
if one of them are blank, a message should pop up telling them this, and the code should not go any further. I would like all of those reviewed, correct.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,339
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Ok we can fix that so all are checked.

I would suggest an inputbox as opposed to the message box to handle entry of the missing info. That being said, what do you want to happen if the user refuses to enter the missing information for a cell address? Do you want to exit the subroutine if they don't enter the requested information?

Are these checks for missing info being done on the "Input" sheet?
 

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
That works! If a user refuses to enter anything that it simply can't go forward and nothing happens (i.e. if they hit the button to move forward, the same message just keeps popping up).

Yes, these checks are being done on the input sheet
 

Forum statistics

Threads
1,141,576
Messages
5,707,188
Members
421,497
Latest member
philipg

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
Top