If then VBA

[drubin25]

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

 

[johnnyL]

Where is your closure of the 'If' statements?

 

[johnnyL]

Well, in the mean time, I did a quick clean up of some of the 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]

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]

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]

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]

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]

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]

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]

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