# Thread: Select Case and Currency (Excel 97)

1. ## Select Case and Currency (Excel 97)

I am working on a "calculator" that will determine the account grade of a client based on revenue.

I am doing this on a worksheet and have a calculated field "total revenue".

What I need to have is a field that will determine the account grade by the "total revenue" of the particular client.

I know I need to do a "Select Case", but have a hard time w/ this in Excel (I think VBA is much easier in Access, and I am struggling to learn it in Excel.

Basically, what I would like to do is something like this

Select Case

Case CG
If Total Revenue <=\$150,000 Then

Case C1
If Total Revenue >=\$75,000 and<=\$149,999 Then

If Total Revenue >=\$50,000 and <=\$74,999 Then

If Total Revenue >=\$25,000 and >=\$49,999 Then

Else
End Select

(sorry for the massacre of the code, I rely on my VB book for propper scripting, and it is at home)

My questions is this: Do I have to use and identify currency in VB, or just an integer (bear in mind that it is formatted as currency in the worksheet itself.

Secondly, how do I let Excel know what cell it needs to look at in order to calculate a grade, I think it would be something like "Account Grade =(C5) ' -C5 is the total revenue cell.

Any help would be appreciated, I have seen examples of "select case" on the boards, but have not seen examples of it in regards to currency.

Thanks

2. ## Re: Select Case and Currency (Excel 97)

kallen

OK here it is in VBA for Excel:

Select Case TotalRevenue
Case Is <= 15000
Case 75000 To 149999
Case 50000 To 74999
Case 25000 To 49999
Case Else
End Select

No need for the Ifs and Thens, unless you used these for us to understand what you wanted to do.

HTH

Wassim

3. ## Re: Select Case and Currency (Excel 97)

Thanks Wassim,

I will try it out tonight!!!!!

4. ## Re: Select Case and Currency (Excel 97)

kallen

Sorry I hit the Post It button too quickly...

<<< Do I have to use and identify currency in VB, or just an integer (bear in mind that it is formatted as currency in the worksheet itself. >>>

Not sure what you mean, but Excel does not care about the format of the number in the cell, it uses its own internal number, so if that is what you are asking, you can use Integer, double is better since you have a wider range.

<<< Secondly, how do I let Excel know what cell it needs to look at in order to calculate a grade, I think it would be something like "Account Grade =(C5) ' -C5 is the total revenue cell. >>>
Call that cell TotalRevenue and then in the code I sent you change to:

Select Case Range("TotalRevenue").Value

<<< ... but have not seen examples of it in regards to currency. >>>

Its irrelevant, a Case Statement compares one expression to several different values. Nothing to do with the actual "meaning" of these values.

HTH.

Wassim

5. ## Re: Select Case and Currency (Excel 97)

Hi,
Is there a particular reason you want to do this with VBA? You could achieve the same thing with a simple LOOKUP function and a table of revenues and grades, which would probably be simpler to maintain.
If you're interested and need any help doing it, please repost.
Hope that helps.

6. ## Re: Select Case and Currency (Excel 97)

Thanks for pointing that out to me. I have no experience with "Lookup" functions, so tend to ignore them. (although I am working on a SIC Code "lookup" directory, and think that this method would be VERY useful.

My problem with my current project is that the end users are salespeople, who don't like being asked to to so called administrative functions. What I want is for the salesperson to enter the total revenue (for a particular customer) and at the touch of a command button "calculate grade" it is automatically done for them. If I ask them to do lookups, I can guarantee they will not meet their deadline (gotta love it).

Do you have any '"lookup pointers" that may help me in either of my projects. I tried experimenting with VLookup, and always have problems.

7. ## Re: Select Case and Currency (Excel 97)

Hi Wassim,

I used the code you gave me and I got NO error messages!! (this is very good!!) My only problem now is the macro/function is not calculating the AccountGrade for me. I have a command button on my worksheet that says "Calculate Grade" I used you code as the macro for the button. When I run it, I get nothing, (although I know Excel is thinking in the background, it cannot complete the task). I have both TotalRevenue and AccountGrade named in the worksheet.

I poured over my VBA books, and think maybe I should do a select range or something like that. I tried it out and of course. Nothing happened. Here is my code.

Dim TotalRevenue As Double

Select Case Range("TotalRevenue").Value

Case Is <= 150000
Case 75000 To 149999
Case 50000 To 74999
Case 25000 To 49999
Case Else

End Select

End Sub

Thanks for your help and patience, one of these days I will get it.

8. ## Re: Select Case and Currency (Excel 97)

Hi,
If you have a fixed cell for them to enter the revenue into, then you can put a lookup formula in the next cell which will give them the relevant grade when they enter a revenue value. (Incidentally, your code isn't working because you're not assigning string values to your AccountGrade variable - i.e. it should be AccountGrade = "CG" not AccountGrade=CG - and you're also not actually doing anything with the AccountGrade variable - you need to assign it to a cell or have it displayed in a message box or whatever.)
For SIC codes, you'll want to use a VLOOKUP function to return an exact match for that code though you'll need to be careful as to whether you have several possible descriptions associated with one code - e.g. as best I recall SIC code 1751 can be subdivided into Carpentry, Cabinets or Door/Window depending on how detailed an analysis you want to do.
If you'd like me to put together a sample workbook for either of these (or both!) let me know.
Hope that helps.

9. ## Re: Select Case and Currency (Excel 97)

Just to expand on Rory's comment, here is a way to fix your code for Excel:

Dim TotalRevenue As Double

Select Case Range("TotalRevenue").Value

Case Is <= 150000
Range("A1").Value = "CG"
Case 75000 To 149999
Range("A1").Value = "C1"
Case 50000 To 74999
Range("A1").Value = "C2"
Case 25000 To 49999
Range("A1").Value = "C3"
Case Else
Range("A1").Value = "C4"

End Select

End Sub
</pre>

Of course, that is assuming that the cell you want the resulting grade to show up in is A1. To make it more flexible, you may wish to pass parameters (namely, the cell address in question) to the procedure. Or you could make the procedure a custom function, which you can use in the cell itself.

(The reason Excel is harder than Access is, you are used to the Access object model and not the Excel object model. That's OK, for us, Access is harder...)

10. ## Re: Select Case and Currency (Excel 97)

First, I am assuming that the first "If" in your pseudo code should have read >= not <=. It does not make sense otherwise.

As someone else said, the best way to do this is to use the VLOOKUP worksheet function, you do not need VBA code. I have attached a workbook that has the formula on Sheet1 in the range B1:B20 to give the Account Grade for the amount in the range A1:A20. Changing any value in A1:A20 should automatically adjust the value displayed in the corresponding cell in column B to the correct Account Grade. The table the VLOOKUP function is using is on Sheet2.

11. ## Re: Select Case and Currency (Excel 97)

First of all, THANKS for everyone who helped me with this dilema.

I now realize VLOOKUP can really "rock" on many of the spreadsheets I work on here at work. I now understand how to use it, and will use it all the time now.

Also, thanks for the help with the code. It works perfectly.

#### Posting Permissions

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