Case statement not working

**nygwnj** · 10-26-2009, 10:06 PM

I wrote this short program that parses out last name and zip code from one cell into two cells so I can map the sales data. I flagged each sale as either member, non-member, or neither, based on the prices of the items (members get 25% off). I used the case statement below.

the problem is that for one of the sale items it's getting flagged as "neither" despite being at the members price level. it works for that same item in other records. the quantity at error point is 3 for two of the errors and 6 for the third, but the strange thing is it works for a few other sale records in which the person also bought 3 of that item (the quantity is not the culprit, or sole culprit).

Here's the code. Obviously Price and Quant are the two variables involved. I declared Price as a double and Quant as an integer, even though both have two decimal places in the data file. That way the product of Quant and each items price for one and Price will both have only two decimal places even though it was rounding off that product to two decimal points without declaring either variable. I cut out some of the case statement so as not to exceed the character limit.

Select Case Price
            
            Case 6.71 * Quant
                If (Item_name = "Hudson Palisades Map Set" Or Item_name = "West Hudson Map Set" Or Item_name = "Iron Mine Trails" Or Item_name = "Circuit Hikes in New Jersey") And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 8.95 * Quant
                If (Item_name = "Hudson Palisades Map Set" Or Item_name = "West Hudson Map Set" Or Item_name = "Iron Mine Trails" Or Item_name = "Circuit Hikes in New Jersey") And Item_name <> "Shipping" Then
                    mbr_sts = "X"
                End If
            Case 7.46 * Quant
                If (Item_name = "North Jersey Map Set" Or Item_name = "Harriman - Bear Mountain Map Set") And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 9.95 * Quant
                If (Item_name = "North Jersey Map Set" Or Item_name = "Harriman - Bear Mountain Map Set") And Item_name <> "Shipping" Then
                    mbr_sts = "X"
                End If
            Case 8.21 * Quant
                If (Item_name = "East Hudson Map Set" Or Item_name = "Shawangunk Map Set") And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 10.95 * Quant
                If (Item_name = "East Hudson Map Set" Or Item_name = "Shawangunk Map Set") And Item_name <> "Shipping" Then
                    mbr_sts = "X"
                End If
            Case 9.71 * Quant
                If (Item_name = "Kittatinny Combo Map Set" Or Item_name = "Doodletown" Or Item_name = "Catskill Day Hikers For All Seasons") And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 12.95 * Quant
                If (Item_name = "Kittatinny Combo Map Set" Or Item_name = "Doodletown" Or Item_name = "Catskill Day Hikers For All Seasons") And Item_name <> "Shipping" Then
                    mbr_sts = "X"
                End If
            Case 11.21 * Quant
                If (Item_name = "Catskills Trails Map Set" Or Item_name = "Best Hikes w/ Children in the Catskills") And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 13.95 * Quant
                If Item_name = "Scenes and Walks - Soft Cover" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
                End If
            Case 6.71 * Quant
                If Item_name = "Iron Mine Trails" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
                        Case 15.3 * Quant
                If Item_name = "Harriman Trails Guide" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 10.16 * Quant
                If Item_name = "Catskills Trails Map Set" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 13.45 * Quant
                If Item_name = "Catskills Trails Map Set" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 12.53 * Quant
                If Item_name = "Guide to the Catskills" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 16.9 * Quant
                If Item_name = "Guide to the Catskills" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 14.35 * Quant
                If Item_name = "New Jersey Walk Book" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 17.95 * Quant
                If Item_name = "New Jersey Walk Book" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 16.6 * Quant
                If Item_name = "New York Walk Book05" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 20.65 * Quant
                If Item_name = "New York Walk Book05" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 7.09 * Quant
                If Item_name = "Shawangunk Map Set" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
              Case 11.3 * Quant
                If Item_name = "Kittatinny Trails - book" And Item_name <> "Shipping" Then
                    mbr_sts = "Y"
                End If
            Case 16.3 * Quant
                If Item_name = "Kittatinny Trails - book" And Item_name <> "Shipping" Then
                    mbr_sts = "X"
            End If
            Case 0.85 * Quant
                If Item_name = "Decal - Trail Conference" Then
                    mbr_sts = "N"
                End If
            Case 2.5 * Quant
                If Item_name = "Conference Logo Patch" Then
                    mbr_sts = "N"
                End If
            Case 2.75 * Quant
                If Item_name = "Long Path Logo Patch" Then
                    mbr_sts = "N"
                End If
            Case 3.75 * Quant
                If Item_name = "Baseball Cap - Retail" Then
                    mbr_sts = "Y"
                End If
            Case 5 * Quant
                If Item_name = "Baseball Cap - Retail" Then
                    mbr_sts = "X"
                End If
            Case 9 * Quant
                If Item_name = "TC - Fall 2002 Collection Notecards" Then
                    mbr_sts = "Y"
                End If
            Case 12 * Quant
                If Item_name = "TC - Fall 2002 Collection Notecards" Then
                    mbr_sts = "X"
                End If
            Case 14.93 * Quant
                Item_name = Left(Item_name, 10)
                If Item_name = "Polo Shirt" Then
                    mbr_sts = "Y"
                End If
            Case 19.9 * Quant
                Item_name = Left(Item_name, 10)
                If Item_name = "Polo Shirt" Then
                    mbr_sts = "X"
                End If
            Case 22.43 * Quant
                Item_name = Left(Item_name, 11)
                If Item_name = "Denim Shirt" Then
                    mbr_sts = "Y"
                End If
            Case 29.9 * Quant
                Item_name = Left(Item_name, 11)
                If Item_name = "Denim Shirt" Then
                    mbr_sts = "X"
                End If
            Case Else
                If Item_name = "Shipping" Then
                    mbr_sts = "N"
                Else
                    mbr_sts = "N"
                End If
            
            
            End Select
                    
                    
        ActiveCell.Value = mbr_sts

If the entire program is required, with or without a data file, I can post that later. I was hoping someone would have had a similar problem and recognize an answer without viewing the whole kit and kaboodle.

Thanks, gary

**Leith Ross** · 10-27-2009, 01:48 AM

Hello Gary,

The most obvious thing I notice is your case statements don't appear to be in any particular order. When using numbers with Case statements, you should arrange the numbers either by frequency of occurrence or in ascending or descending order.

**DonkeyOte** · 10-27-2009, 03:33 AM

Gary,

as Leith says, order in terms of frequency (to reduce amount of evaluations - ie once found Select Case will exit so makes sense to front load most likely options) but also ensure you do all evaluations for each choice in one block, don't repeat the 6.71 again as you have done in your example... only the first test will evaluate thereafter the Select Case will exit,eg

Case 6.71
    If a=b Then result = "X"
Case 6.71
    If a=c Then result = "Y"
Case 6.71
    result = "Z"

If the value is 6.71 only the first will evaluate and then the Select Case will exit (like an IF), so you should use

Case 6.71
   If a=b Then 
       result = "X"
   ElseIf a=c Then 
       result = "Y"
   Else
       result = "Z"
   End If

you also mention decimal significance etc... not entire clear what you mean but I would suspect there could also be issues with precision and I wonder if perhaps it would be easier were you to use:

Select Case Round(Price / Quant,2)
    Case 6.71

    Case ...

End Select

**nygwnj** · 10-27-2009, 09:55 PM

Thanks Leith and Donkeyote. I actually had it ordered in ascending order within groups - the maps came first, then the books, then the combo items (book and map), then novelty items. I had to cut over half out because I exceeded the character limit for a post.

I was aware of the double listing problem and had caught an error in which the correct case didn't execute because it had gone into a preceding case, but had corrected it for that one, but obviously not all of the duplicate prices. I will fix that, and also try the Round function.

I hope it works because posting the whole program will also exceed the character limit, and my guess is a file attachment has a limit too small for the whole excel file, code and all. I could always have a data file of just 10 or so records (instead of the thousands it has) that included the ones the program fails on.

Gary

**nygwnj** · 10-31-2009, 01:41 AM

You were right - the Round function worked. I don't understand why because the program was giving wrong results on only some of the quantities. All the quantities had two decimal places (both zero) and all the prices had two decimal places so without the Round it should have failed on all the records, but it did so only for three as far as the East Hudson trail map item goes. Two were quantity three and one quantity six. it worked for the other ones with quantity three.

I'll just toss this up to a gremlin in the code - sometimes the values must have been stored with more decimal places, sometimes with fewer, and with the Round the ones with more were simply truncated to two places so it worked.

In any event, thanks again,

Gary