# Complex If/Then

#### drubin25

##### New Member
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

#### Attachments

• Annotation 2021-09-02 091626.png
53.4 KB · Views: 1

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

##### Well-known Member
Try This:
VBA Code:
``````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``````

Replies
5
Views
103
Replies
23
Views
259
Replies
0
Views
45
Replies
7
Views
283
Replies
5
Views
662

1,141,284
Messages
5,705,502
Members
421,399
Latest member
hjweiss00

### 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.

### Which adblocker are you using?

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

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