Complex If/Then

drubin25

New Member
Joined
Mar 19, 2016
Messages
38
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
    Annotation 2021-09-02 091626.png
    53.4 KB · Views: 1

Excel Facts

Links? Where??
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.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,355
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

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