Complex If/Then

[drubin25]

I am having difficulty creating if/then statements that are complex. How do I write the following rules?

Rule 1: IF (“input tab” J4)= A, “DO NOT CREATE A SPECIAL BATCH”), if not….

Rule 2: IF (“input tab” J4)= b, AND NOW() < 2pm, “CREATE SPECIAL BATCH”, OTHERWISE “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not…

Rule 3: IF (“input tab” J4)= c, AND NOW() < 2pm, “CREATE SPECIAL BATCH”, OTHERWISE “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not…

Rule 4: IF (“input tab” J4)= d, AND NOW() < 2pm, AND ((“input tab” g7 – “input tab” M7) ÷(“input tab” g7)) <=95%, “CREATE SPECIAL BATCH”, otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

Rule 5: IF (“input tab” J4)= e, AND (“input tab” M7)>=10 AND NOW() <2pm, “CREATE SPECIAL BATCH”, otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

Rule 6: IF (“input tab” J4)= f, AND (“input tab” M7)<=20 AND NOW() <2pm, “CREATE SPECIAL BATCH”, otherwise IF (“input tab” J4)= f AND (“input tab” M7)>20 AND NOW() <2pm AND ((“input tab” g7 – “input tab” M7) ÷(“input tab” g7)) <=90%, “CREATE SPECIAL BATCH”, otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

Rule 7: IF (“input tab” J4)= G AND(“input tab” M7)>20 AND NOW() <2pm AND ((“input tab” g7 – “input tab” M7) ÷(“input tab” g7)) <=95%, “CREATE SPECIAL BATCH”, otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

Rule 8: IF (“input tab” J4)= H AND (“input tab” M7)<=20 and NOW()<2pm, “CREATE SPECIAL BATCH”, otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

Rule 9: IF (“input tab” J4) IS ANYTHING OTHER THAN A, B, C , D, E, F, G, H and ÷(“input tab” g7) <= 20, and now()<2PM, “CREATE BACKORDER”, otherwise if ((“input tab” g7 – “input tab” M7) ÷(“input tab” g7)) <=95%, AND NOW()<2pm, “CREATE SPECIAL BATCH” otherwise “GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER” if not….

For the responses if certain conditions are met, I would like to have pop up messages appear, then have this code next:

Sub SENDTOLOG_Click()



Application.ScreenUpdating = False



Dim copysheet As Worksheet

Dim pastesheet As Worksheet



Set copysheet = Worksheets("Input")

Set pastesheet = Worksheets("Records")



'Copy data from the INPUT to the RECORDS worksheet



'copy date

copysheet.Range("G4").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues



'copy company name

copysheet.Range("J4").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues



'copy credit amount

copysheet.Range("G7").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues



'copy invoice number

copysheet.Range("M7").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues



'copy invoice number

copysheet.Range("G11").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues



'copy invoice number

copysheet.Range("G14").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 5).PasteSpecial xlPasteValues



'copy invoice number

copysheet.Range("D17").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 6).PasteSpecial xlPasteValues



'copy invoice number

copysheet.Range("D20").Copy

pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).PasteSpecial xlPasteValues



'insert basic status description into STATUS cell

'pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).Value = "WAITING FOR CREDIT CONFIRMATION"



'select payment method cell

'pastsheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Select





Application.CutCopyMode = False

Application.ScreenUpdating = True



Sheets("Records").Activate



End Sub

 

[maabadi]

Try This:

Sub SENDTOLOG_Click()
Application.ScreenUpdating = False
Dim CS As Worksheet, PS As Worksheet, Lr As Long, N As Double
N = Now - Date
Set CS = Worksheets("Input")
Set PS = Worksheets("Records")
Select Case UCase(CS.Range("J4").Value)

Case "A"
    MsgBox "DO NOT CREATE A SPECIAL BATCH"
Case "B"
If N < 14 / 24 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "C"
If N < 14 / 24 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "D"
If N < 14 / 24 And (CS.Range("G7").Value - CS.Range("M7").Value) / CS.Range("G7").Value <= 0.95 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "E"
If N < 14 / 24 And CS.Range("M7").Value >= 10 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "F"
If N < 14 / 24 And CS.Range("M7").Value <= 20 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "G"
If N < 14 / 24 And CS.Range("M7").Value > 20 And (CS.Range("G7").Value - CS.Range("M7").Value) / CS.Range("G7").Value <= 0.95 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case "H"
If N < 14 / 24 And CS.Range("M7").Value <= 20 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
Case Else
If N < 14 / 24 And CS.Range("G7").Value <= 20 Then
    MsgBox "CREATE BACKORDER"
ElseIf N < 14 / 24 And (CS.Range("G7").Value - CS.Range("M7").Value) / CS.Range("G7").Value <= 0.95 Then
    MsgBox "CREATE SPECIAL BATCH"
Else
    MsgBox "GIVE TICKET TO FRONT OFFICE TO CREATE A BACKORDER"
End If
End Select


Lr = PS.Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy data from the INPUT to the RECORDS worksheet
'copy date
PS.Range("A" & Lr).Value = CS.Range("G4").Value
PS.Range("B" & Lr).Value = CS.Range("J4").Value
'copy credit amount
PS.Range("C" & Lr).Value = CS.Range("G7").Value
'copy invoice number
PS.Range("D" & Lr).Value = CS.Range("M7").Value
'copy invoice number
PS.Range("E" & Lr).Value = CS.Range("J11").Value
'copy invoice number
PS.Range("F" & Lr).Value = CS.Range("G14").Value
'copy invoice number
PS.Range("G" & Lr).Value = CS.Range("D17").Value
'copy invoice number
PS.Range("H" & Lr).Value = CS.Range("D20").Value
'insert basic status description into STATUS cell
'PS.Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).Value = "WAITING FOR CREDIT CONFIRMATION"
'select payment method cell
'pastsheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets("Records").Activate
End Sub