Solved: Select Case with multiple criteria

[simora]

I have a worksheet where I am trying to determine a price based on 2 criteria, Size and Shading.
How can I set up a select case statement with multiple criteria, or is there a better way to determine a price on the worksheet.
My Sample data is arranged like this on a seperate sheet;

Size B/W Color1/16 Page115.00125.001/12 Page135.00155.001/10 Page140.00175.001/8 Page195.00220.001/6 Page225.00275.001/4 Page323.00363.001/3 Page400.00450.001/2 Page582.00632.001 Full Page812.00862.00Trade Page1.001.00

If all else fails, or if its a better solution, how should I code this to fit into an existing Excel 2002 UserForm.

Thanks

[simora]

Sorry:

Formatting issues: See attached Excel 2003 sample worksheet called PriceList.xls

[austenr]

Could use a few examples. are there going to be part page color and black and white? Assume so. Do the parts add up to a whole page?

Should be easy enough after we can assertaine the problem.

[simora]

Sorry I didn't explain that there is only 1 possible size selection at a time from Col A and then either B/W OR Color .

I was hoping to either integrate this into a Frame on an existing Excel UserForm, or try to create a worksheet solution. The UserForm may be a cleaner solution. Hope I explained it OK >

[mikerickson]

You might take a look at this workbook.
There is a worksheet approach with two cells having list validation.
There is also a Userform that will put the price in a cell based on the users choice of size and coloring.


<See Attachment below>

[shrivallabha]

I tried to do with Multiple select conditions with And as

[vba]Select Case .Range("A" & i).Value And .Range("B" & i).Value
Case "1/16 Page" And "B/W"
Mycode
Case Else
End Select[/vba]

Using And statement makes it error out on Type 13. I think 'And' does not work in tandem with 'Select' statement.

[mikerickson]

.Range("A" & i).Value And .Range("B" & i).Value will evaluate to an error, unless both the cells have a Logic value in them

Similarly "1/16 Page" And "B/W" will always error, since And does not operate on strings.


Perhaps instead of using the logical connector AND, you might use the string cocatenation operator &

[VBA]Select Case .Range("A" & i).Value A & .Range("B" & i).Value
Case "1/16 Page" & "B/W"
Mycode
End Select[/VBA]

By the way, did you look at the code and formulas in the workbook I attached?

Select Case is not the best way to go about this.

Use the coloring selected to decide which column and then use VLOOKUP to search for the size and return the price.

[simora]

mikerickson:

THANKS !
The worksheet approach is workable and appreciated, however, there is no useable UserForm.
This is the error that I'm getting.
Workbook has lost its VBA project , ActiveX controls and programmability - related features.
The macro 'PriceList2.xls!makeUF' cannot be found.

I would've liked to see that in action so that I could integrate it into the existing userform on a different page. I had the same problems as shrivallabha trying to use the Select Case approach.

[mikerickson]

When I get home, I'll check the attachment out are re-upload it.

The userform used the two option buttons "B/W" and "Color" to control the TextColumn property of a list box containing the sizes.

A direct analogy to the worksheet method, but using VBA and Userform properties.

[mikerickson]

I'm not sure how that file got corrupted.

Try this one.


(Note the comment when you choose to put the value in a cell with the userform.)

[shrivallabha]

.Range("A" & i).Value And .Range("B" & i).Value will evaluate to an error, unless both the cells have a Logic value in them

Similarly "1/16 Page" And "B/W" will always error, since And does not operate on strings.


Perhaps instead of using the logical connector AND, you might use the string cocatenation operator &

[vba]Select Case .Range("A" & i).Value A & .Range("B" & i).Value
Case "1/16 Page" & "B/W"
Mycode
End Select[/vba]

By the way, did you look at the code and formulas in the workbook I attached?

Select Case is not the best way to go about this.

Use the coloring selected to decide which column and then use VLOOKUP to search for the size and return the price.

Thank you for the kind advice mikerickson, the concatenation operator does the trick. And yes, Select statement will require a lot of case-based formulating to make the whole thing work.

[simora]

mikerickson

Thanks :
Works great.

shrivallabha

22 If Else IF statements could also work. ( Lots of formatting)

[shrivallabha]

mikerickson

Thanks :
Works great.

shrivallabha

22 If Else IF statements could also work. ( Lots of formatting)

Yes, you are right about 'If-Else' as well. It makes sense only if you are going to do the coding once (i.e. without any intent of having to edit in future) and use forever.