Excel 201 rounding creating a problem with my macro

I am trying to get the OilTotal cell to change based upon the entry in the OilQuantity cell. I’m sure this has to do with rounding. Can someone help debug my macro or make it easier to read/understand?  See attached file.

Item             Qty                            Unit Price              Total
Cake Mix      27                             $2.00              $54.00
Oil (48 oz)      72.00000009             $2.00              $2.00

Sub Recalculate()
'
' Recalculate Macro
'
    If OilQuantity = 2.66666667 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 5.33333334 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 8.00000001 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 10.66666668 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 13.33333335 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 16.00000002 Then OilTotal.Formula = "=OilUnitPrice * 1"
    If OilQuantity = 18.66666669 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 21.33333336 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 24.00000003 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 26.6666667 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 29.33333337 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 32.00000004 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 34.66666671 Then OilTotal.Formula = "=OilUnitPrice * 2"
    If OilQuantity = 37.33333338 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 40.00000005 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 42.66666672 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 45.33333339 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 48.00000006 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 50.66666673 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 53.3333334 Then OilTotal.Formula = "=OilUnitPrice * 3"
    If OilQuantity = 56.00000007 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity = 58.66666674 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity = 61.33333341 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity = 64.00000008 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity = 66.66666675 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity = 69.33333342 Then OilTotal.Formula = "=OilUnitPrice * 4"
    If OilQuantity > 72.00000009 Then OilTotal.Formula = "=OilUnitPrice * 5"
End Sub
Test.xltm
cowboywmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You don't need to be that specific. Try something like this:
Select Case Range("OilQuantity").Value
    Case Is > 72
        Range("OilTotal").Formula = "=OilUnitPrice * 5"
    Case Is > 56
        Range("OilTotal").Formula = "=OilUnitPrice * 4"
    Case Is > 37
        Range("OilTotal").Formula = "=OilUnitPrice * 3"
    Case Is > 18
        Range("OilTotal").Formula = "=OilUnitPrice * 2"
    Case Else
        Range("OilTotal").Formula = "=OilUnitPrice * 1"
End Select

Open in new window

Note that I'm guessing as to the breakpoints. Also note that with Select Case, the first Case (top to bottom) that's True is the one used.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cowboywmAuthor Commented:
Worked great.  Need to get more information on the Case function.

Thank you.
0
Martin LissOlder than dirtCommented:
Here's something I copied from the web. BTW, please don't forget to close this question.

One of the best methods for multiple conditions is the Select Case Statement. It does away with the need for multiple If Statements which can make Excel VBA code very hard to read and decipher. The Select Case Statement has a syntax of:

Select Case < Expression to test>

    Case 

      Do something

    Case Else

      Do something else

End Select
As you can see the Select Case Statement is very similar to the If Statement in that it will only perform some action if/when a condition is met. However, as you will learn, the Select Case is far more flexible. Let's look at the "Select Case" Statement in it's simplest form.

Sub TheSelectCase1()

    Select Case Range("A1").Value

          Case 100

              Range("B1") = 50

    End Select

End Sub
Now let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use;

Sub TheSelectCase2()

    Select Case Range("A1").Value

          Case 100

              Range("B1").Value = 50

          Case 150

              Range("B1").Value = 40

          Case 200

              Range("B1").Value = 30

          Case 350

              Range("B1").Value = 20

          Case 400

              Range("B1").Value = 10

    End Select

End Sub
This, in my opinion, is a far better structure and easier to read than an If Statement with multiple ElseIf Statements. If none of the above Conditions were met, nothing would occur, unless we use the optional Case Else Statement, like:

Sub TheSelectCase3()

    Select Case Range("A1").Value

          Case 100

              Range("B1").Value = 50

          Case 150

              Range("B1").Value = 40

          Case 200

              Range("B1").Value = 30

          Case 350

              Range("B1").Value = 20

          Case 400

              Range("B1").Value = 10

          Case Else

              Range("B1").Value = 0

    End Select

End Sub
So if the value of range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1. 

Now while this demonstrates how we can check multiple conditions with the Select Case Statement, what if we want to perform some action if the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun intended) we could use:

Sub TheSelectCase4()

    Select Case Range("A1").Value

          Case 100, 150, 200, 350, 400

              Range("B1").Value = Range("A1").Value

          Case Else

              Range("B1").Value = 0

    End Select

End Sub
Let's assume that you only want to perform some action if the range(s) you are checking are between 2 numbers. If this is the case (excuse the pun) then you could use:

Sub TheSelectCase5()

    Select Case Range("A1").Value

        Case 100 To 500

             Range("B1").Value = Range("A1").Value

        Case Else

             Range("B1").Value = 0

      End Select

End Sub
In this instance, if the range A1 contains a number => 100 and =< 500 then cell A1 value will be placed into cell B1.

Now, what about if we needed to check if cell A1 was not only between 100 and 500 but also between 700 and 1000, 1500 and 2000? No problem, with the Select Case you would simply use:


Sub TheSelectCase6()

    Select Case Range("A1").Value

        Case 100 To 500, 700 To 1000, 1500 To 2000

             Range("B1").Value = Range("A1").Value

       Case Else

             Range("B1").Value = 0

     End Select

End Sub
In other words if cell A1 contains a number (eg 600) that does not meet the Case criteria, B1 will equal 0. By-the-way, the limit using a Select Case like this is far from 3 criteria! You can also mix and match the criteria you use in a Select Case Statement, so if you wanted to include some text and/or specific numbers you could use:

Sub TheSelectCase()

    Select Case Range("A1").Value

           Case 100 To 500, 652, 700 To 1000, 1233, 1500 To 2000, "dog", "cat"

                 Range("B1").Value = Range("A1").Value

           Case Else

                 Range("B1").Value = 0

     End Select

End Sub
Select Case Knows its Alphabet

The last benefit we will look at with the Select Case Statement is how we can use it on text and determine if it lies between other text in an alphabetical sense

Sub TheSelectCase7()

    Select Case Range("A1").Text

          Case "aardvark" To "elephant"

                 Range("B1").Value = "it's between"

          Case Else

                 Range("B1").Value = "it's not between"

         End Select

End Sub

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cowboywmAuthor Commented:
Thank you for the lesson and documentation.
0
Martin LissOlder than dirtCommented:
Here's another way to use Select case that I find useful.
Select Case True
    Case Range("A1") = 50
        MsgBox "OK"
    Case Range("B1") = Empty
        MsgBox "Please don't forget to close the question"
    Case ActiveSheet.UsedRange.Rows.Count > 10000
        MsgBox "too many"
End Select

Open in new window

0
Martin LissOlder than dirtCommented:
I believe that cowboywm meant to select my answer rather than his "Worked great" post.
0
cowboywmAuthor Commented:
Great suggestions.  Thank you.
0
cowboywmAuthor Commented:
Worked great. Thank you.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.