Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: With Statement

  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 is a way to make this more concise. Can someone give me the code to do so. Here is what I have:

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also depend on how many of these
    values can br true at once.
    If only one of a group can be true
    then add an Exit Sub or Goto to each
    If..Then to skip the other If..Thens



    With Sheets("Order Table")
    If OPTPan.Value Then
    .Range("C" & intcurrentrow).Value = "Personal"
    .Range("D" & intcurrentrow).Value = 4.99 + 0.5 * bytToppings
    End If
    If OPT10inch.Value Then
    .Range("C" & intcurrentrow).Value = "10 inch"
    .Range("D" & intcurrentrow).Value = 5.99 + 0.5 * bytToppings
    End If
    If OPT12inch.Value Then
    .Range("C" & intcurrentrow).Value = "12 inch"
    .Range("D" & intcurrentrow).Value = 7.99 + 0.5 * bytToppings
    End If
    If OPT14inch.Value Then
    .Range("C" & intcurrentrow).Value = "14 inch"
    .Range("D" & intcurrentrow).Value = 9.99 + 0.5 * bytToppings
    End If
    If OPT16inch.Value Then
    .Range("C" & intcurrentrow).Value = "16 inch"
    .Range("D" & intcurrentrow).Value = 10.99 + 0.5 * bytToppings
    End If
    If OPTSicilian.Value Then
    .Range("C" & intcurrentrow).Value = "Sicilian"
    .Range("D" & intcurrentrow).Value = 11.5 + 0.5 * bytToppings
    End If
    End With
    End If




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

    I have declared the values of your option boxes directly in this code. You should already have these values available, so it is here so I could test the sample without creating anything else.

    Code:
    Sub test()
    Dim MyArray, x, y, z
    Dim OPTPan, OPT10inch, OPT12inch
    Dim OPT14inch, OPT16inch, OPTSicilian
    
    OPTPan = 0
    OPT10inch = 0
    OPT12inch = 0
    OPT14inch = 0
    OPT16inch = 1
    OPTSicilian = 0
    
    MyArray = Array(OPTPan, OPT10inch, OPT12inch, OPT14inch, OPT16inch, OPTSicilian)
    x = Application.Match(1, 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
    Bye,
    Jay

    EDIT: You could also:

    Dim MyArray(1 to 6)
    MyArray(1) = OPTPan.Value
    MyArray(2) = OPT10inch.Value
    .
    .
    .


    These may not work without a bit of tinkering, but I think it would be pretty cool to do this.

    [ This Message was edited by: Jay Petrulis on 2002-05-07 20:50 ]

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
  •