Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Account Grade is CG

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

    If Total Revenue >=$50,000 and <=$74,999 Then
    Account Grade is C2

    If Total Revenue >=$25,000 and >=$49,999 Then
    Account Grade is C3

    Else
    Account Grade is C4.
    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. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Select Case and Currency (Excel 97)

    kallen

    OK here it is in VBA for Excel:

    Select Case TotalRevenue
    Case Is <= 15000
    AccountGrade = CG
    Case 75000 To 149999
    AccountGrade = C1
    Case 50000 To 74999
    AccountGrade = C2
    Case 25000 To 49999
    AccountGrade = C3
    Case Else
    AccountGrade = C4
    End Select

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

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case and Currency (Excel 97)

    Thanks Wassim,

    I will try it out tonight!!!!!

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    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
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks for your help!!

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Sub AccountGrade()
    Dim TotalRevenue As Double
    Dim AccountGrade As String

    Select Case Range("TotalRevenue").Value

    Case Is <= 150000
    AccountGrade = CG
    Case 75000 To 149999
    AccountGrade = C1
    Case 50000 To 74999
    AccountGrade = C2
    Case 25000 To 49999
    AccountGrade = C3
    Case Else
    AccountGrade = C4

    End Select

    End Sub

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

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Case and Currency (Excel 97)

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

    <pre>Sub AccountGrade()
    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. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Attached Files Attached Files
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Nov 2001
    Location
    Massachusetts
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •