With Statement

[TheMatrixReloaded]

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

 

[Legacy 98055]

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

<pre>

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


</pre>

 

[Jay Petrulis]

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.

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