Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help With Making If Statements shorter.

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Baltimore
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay,

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

    James

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Baltimore
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •