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]

Ok set G4 = "" and then test the following to see if it does what you want:

Sub test()
'
    Application.ScreenUpdating = False
'
    Dim CS As Worksheet, PS As Worksheet, lr As Long        ' , M As Long
    Dim ArSourceAddress()   As Variant
    Dim CustName            As String
'
    Set CS = Worksheets("Input")
    Set PS = Worksheets("Records")
'
    If CS.Range("G4") = "" Then
        Do
            CustName = Application.InputBox(Prompt:="Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=2)  ' Text entry
        Loop Until CustName <> vbNullString And CustName <> False
'
        CS.Range("G4").Value = CustName
    End If
End Sub

That code should prevent the user from cancelling, and clicking the X button at the top right of the box.

Is that what you want? To keep asking for the info until the info is entered?

The code only checks G4. I wanted to make sure that works the exact way you want, prior to me doing anything else.

 

[drubin25]

It says "Expected end with"

 

[drubin25]

I got the end with fixed, but when one of those cells is bank (including G4) it continues to run. it does not tell the operator that data needs to be entered,

 

[johnnyL]

@drubin25 One step at a time. The last code I provided is a separate module to test just the G4 cell. It does not get put into your other code, it is a separate module. There is no missing end with because it doesn't have a with to begin with.

Test it as a separate module. It is designed to see if it handles the missing G4 entry only.

Let me know if that is how you want G4 handled.

 

[drubin25]

ah got it. Yes, you are correct. This is how I would like it to work. Thank you!

 

[johnnyL]

Is the PO NUMBER strictly numbers only or can there be letters or other stuff?

 

[johnnyL]

Same question for the 'SPECIAL BATCH TICKET NUMBER'.

 

[drubin25]

Is the PO NUMBER strictly numbers only or can there be letters or other stuff?

there can be letters as well

 

[drubin25]

Same question for the 'SPECIAL BATCH TICKET NUMBER'.

it could be all numbers, or there could be letters

 

[johnnyL]

Ok I think this incorporates all of the code that you have provided thus far. Test it out and let us know if anything needs corrected.

Sub test2()
'
''    Application.ScreenUpdating = False
'
    Dim CS As Worksheet, PS As Worksheet, lr As Long        ' , M As Long
    Dim LineQuantity        As Long
    Dim QtyRejected         As Long
    Dim TicketLoadDate      As String
    Dim ArSourceAddress()   As Variant
    Dim CustName            As Variant
    Dim POnumber            As Variant
    Dim RejectedBy          As Variant
    Dim SpecialBatchNumber  As Variant
'
    Set CS = Worksheets("Input")
    Set PS = Worksheets("Records")
'
    If CS.Range("G4") = "" Then
        Do
            CustName = Application.InputBox(Prompt:="Please enter the CUSTOMER NAME" & vbCrLf & " " & vbCrLf & "XXXXXXX")  ' Text entry
        Loop Until CustName <> vbNullString And CustName <> False
'
        CS.Range("G4").Value = CustName
    End If
'
    If CS.Range("G7") = "" Then
        Do
            LineQuantity = Application.InputBox(Prompt:="Please enter the LINE QUANTITY" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=1)  ' Numeric Entry
        Loop Until LineQuantity <> 0 And LineQuantity <> False
'
        CS.Range("G7").Value = LineQuantity
    End If
'
    If CS.Range("M7") = "" Then
        Do
            QtyRejected = Application.InputBox(Prompt:="Please enter the QTY REJECTED" & vbCrLf & " " & vbCrLf & "XXXXXXX", Type:=1)    ' Numeric Entry
        Loop Until QtyRejected <> 0 And QtyRejected <> False
'
        CS.Range("M7").Value = QtyRejected
    End If
'
    If CS.Range("G14") = "" Then
        Do
            TicketLoadDate = Application.InputBox(Prompt:="Please enter the TICKET LOAD DATE" & vbCrLf & " " & vbCrLf & "XXXXXXX")      ' Date Entry
        Loop Until IsDate(TicketLoadDate) = True
'
        CS.Range("G14").Value = TicketLoadDate
    End If
'
    If CS.Range("M14") = "" Then
        Do
            RejectedBy = Application.InputBox(Prompt:="Please enter your name in the REJECTED BY: BOX" & vbCrLf & " " & vbCrLf & "XXXXXXX") ' Text entry
        Loop Until RejectedBy <> vbNullString And RejectedBy <> False
'
        CS.Range("M14").Value = RejectedBy
    End If
'
    If CS.Range("G20") = "" Then
        Do
            POnumber = Application.InputBox(Prompt:="Please enter the PO NUMBER" & vbCrLf & " " & vbCrLf & "XXXXXXX")  ' Text entry
        Loop Until POnumber <> vbNullString And POnumber <> False
'
        CS.Range("G20").Value = POnumber
    End If
'
    If CS.Range("D26").Value = "CREATE SPECIAL BATCH" Then
        MsgBox ("YOU MUST CREATE A SPECIAL BATCH")
'
        Do
            SpecialBatchNumber = Application.InputBox(Prompt:="ENTER THE SPECIAL BATCH TICKET NUMBER THAT WAS CREATED: " & vbCrLf & "XXXXXXX:") ' Text entry
        Loop Until SpecialBatchNumber <> vbNullString And SpecialBatchNumber <> False
'
        lr = PS.Range("A" & PS.Rows.Count).End(xlUp).Row + 1            ' Find First Empty Row after Data

        PS.Range("Q" & lr).Value = SpecialBatchNumber
    End If
'
    If CS.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.")
    End If
'
    If CS.Range("D26").Value = "SEND TO OFFICE TO CREATE A BACKORDER." Then
        lr = PS.Range("A" & PS.Rows.Count).End(xlUp).Row + 1            ' Find First Empty Row after Data
'
'       You didn't mention what to do here with the lr
'
        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
    ArSourceAddress = Array("M4", "G4", "D17", "G14", "G7", "M7", "P7", "G11", "D26", "M14")
'
    For I = 0 To UBound(ArSourceAddress)
        PS.Cells(lr, I + 1).Value = CS.Range(ArSourceAddress(I)).Value           ' Columns 1 thru 10 .... Array addresses
    Next
'
    PS.Cells(lr, 11).Resize(, 4).Value = CS.Range("S24").Resize(, 4).Value  'Columns 11,12,13,14 .... S24,T24,U24,V24
'
    PS.Cells(lr, 15).Resize(, 2).Value = CS.Range("X24").Resize(, 2).Value  ' Columns 15,16 .... X24,Y24

''Application.ScreenUpdating = True
End Sub