Help With Making If Statements shorter.

TheMatrixReloaded

New Member
Joined
May 6, 2002
Messages
29
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.

Code:
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
 
Upvote 0
Hi Jay,

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

James
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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
Back
Top