Help With Making If Statements shorter.

[TheMatrixReloaded]

I know there's a way to make these If statements into one With statement or Select statement. If you can help, it will be greatly appreciated. Here is my code.

Private Sub cmdNext_Click()
'**********************************************************
'* Variables are dimensioned for use. *
'* ********************************************************
Dim intcurrentrow As Long, lngcurrentvalue As Long, bytToppings As Byte
Dim strMessageString As String
Dim bytAnotherPizza As Byte
Dim i As Integer
Dim x As Byte
Dim z As Byte
Me.Hide
intcurrentrow = Sheets("Order Table").[a65536].End(xlUp).Offset(1).Row
Sheets("Order Table").Range("A" & intcurrentrow).Value = Time
Sheets("Order Table").Range("B" & intcurrentrow) = myord
For t = 0 To lstToppings.ListCount - 1
If lstToppings.Selected(t) Then
bytToppings = bytToppings + 1
End If
Next t
'**********************************************************
'* In this section several if statements are used to *
'* place the type and price of the pizza into the *
'* corresponding cells on the Order Table. *
'**********************************************************
If OPTPan.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "Personal"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 4.99 + 0.5 * bytToppings
End If
If OPT10inch.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "10 inch"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 5.99 + 0.5 * bytToppings
End If

If OPT12inch.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "12 inch"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 7.99 + 0.5 * bytToppings
End If

If OPT14inch.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "14 inch"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 9.99 + 0.5 * bytToppings
End If

If OPT16inch.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "16 inch"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 10.99 + 0.5 * bytToppings
End If
If OPTSicilian.Value = True Then
Sheets("Order Table").Range("C" & intcurrentrow).Value = "Sicilian"
Sheets("Order Table").Range("D" & intcurrentrow).Value = 11.5 + 0.5 * bytToppings
End If
txtPhoneNumber = Empty
TxtAddress = Empty
Me.lstToppings.Clear
frmOrderType.cboOrderType.Clear
Call PopulateOrderType
For x = 0 To 3
frmOrderType.cboOrderType.AddItem astrOrderType(x)
Next x
frmOrderType.cboOrderType.Width = 90
Application.Run ("popTopper")
For z = 0 To 7
lstToppings.AddItem astrToppings(z)
Next z
Me.lstToppings.Visible = True
'**********************************************************
'* This section simply asks the customer if they would *
'* like to order another pizza. If yes, the order form *
'* is displayed, if no all fields are cleared for the *
'* order. *
'**********************************************************
strMessageString = "Your pizza will be done soon!"
strMessageString = strMessageString & Chr(13)
strMessageString = strMessageString & "Do you want to order another?"
bytAnotherPizza = MsgBox(strMessageString, vbYesNo, "Another?")
If bytAnotherPizza = vbNo Then
Else:
OPTPan = False
OPT10inch = False
OPT12inch = False
OPT14inch = False
OPT16inch = False
OPTSicilian = False
frmOrderType.Show
End If
End Sub

[Jay Petrulis]

Hi,

The code I provided last night *should* work very nicely (if tweaked properly).

Here it is with the variable set to True and False rather than 1 and 0.

Sub test()
Dim MyArray, x, y, z
Dim OPTPan, OPT10inch, OPT12inch
Dim OPT14inch, OPT16inch, OPTSicilian

''''''''''''''''''
' these are given, placed here for testing
OPTPan = False
OPT10inch = False
OPT12inch = False
OPT14inch = False
OPT16inch = True
OPTSicilian = False
''''''''''''''''''''''''''''
''''''''''''''''''''''''''''

MyArray = Array(OPTPan, OPT10inch, OPT12inch, OPT14inch, OPT16inch, OPTSicilian)
x = Application.Match(True, MyArray, 0)
y = WorksheetFunction.Choose(x, "Personal", "10 inch", "12 inch", "14 inch", "16 inch", "Sicilian")
z = WorksheetFunction.Choose(x, 4.99, 5.99, 7.99, 9.99, 10.99, 11.5)

With Sheets("Order Table")
    .Range("C" & intcurrentrow) = y
    .Range("D" & intcurrentrow) = z + 0.5 * bytToppings
End With

End Sub

I encourage you to use this, or a variant of this, for efficiency. Zero if statements.

Bye,
Jay

[James]

Hi Jay,

I like it! I need to study this and start going in this direction. Those "If" statements drive me crazy.

James

[TheMatrixReloaded]

so that code should work? I'm gonna try it, I'll let ya know man. Thanks

[Jay Petrulis]

Hi,

Just to test that it will work, copy the code posted to a new module. Comment out the code where you write to the worksheet.

Make your last bit of code look like this instead.

''''''''''''''''''''''''
MyArray = Array(OPTPan, OPT10inch, OPT12inch, OPT14inch, OPT16inch, OPTSicilian)
x = Application.Match(True, MyArray, 0)
y = WorksheetFunction.Choose(x, "Personal", "10 inch", "12 inch", "14 inch", "16 inch", "Sicilian")
z = WorksheetFunction.Choose(x, 4.99, 5.99, 7.99, 9.99, 10.99, 11.5)

MsgBox x
MsgBox y
MsgBox z
'''''''''''''''''''

And run the sub. The three message boxes should tell you that you have captured the button values correctly (when adjusted to fit the actual file), and that you can do a lot more with these techniques.

An additional note:

Using MATCH in VBA is tricky.

WorksheetFunction.Match rarely works as it should. Application.WorksheetFunction.Match has the same problems.

Always use only Application.Match, as it is stable and not quirky.