Independent Rounding in VBA

[that1guy]

Hi Gurus of Excel,

Hopefully my goal is possible... Here's what I'm shooting for:

I am working on a spreadsheet to calculate sales tax percentages or sales tax amounts. There are 3 cells involved with my attempted calculations. The first is a cell ("D3") formatted for accounting with 2 decimal places for the subtotal. (eg: $495.00) I have a second cell ("B5") formatted for percentages that include 3 decimal places for the applicable sales tax. (eg: 15.275%) I have a third cell ("D5") formatted for accounting with 2 decimal places for the sales tax dollar amount. (eg: $11.27)

I am working on VBA code to calculate either the second or third cell depending on which of the two is filled by the user. (If the user provides the sales tax%, the sales tax $ will be calculated... if the user provides the sales tax $ amount, the sales tax% will be calculated)

The issue I am having is around the rounding calculations I need the code to perform. If the user provides a sales tax % that results in a sales tax $ amount with more than 2 decimal places, the amount must round up. My current code does this, but here's the issue...

I need the value entered in the sales tax % cell to remain as the value entered. Only the sales tax $ value should be altered by the rounding process. My current code will provide the correct rounded sales tax $ amount, but is also rewriting the sales tax % cell with the rounded value.

Can anyone help me figure out a way to have the sales tax % value stay the same while provided the rounded sales tax $ value? I have included the code I am currently running below. Thanks in advance!

(If you're feeling ambitious, there is also another annoying issue I am having where the value entered in the sales tax % cell will automatically at 2 zeros before the decimal point. If I enter 13, the cell shows 1300.000%. If I then enter the value 13 over the cell again, it will format correctly as 13.000%. Weird. )

Thanks Again!

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TaxPerc As Range 'not active
Dim RoundVal As Long 'not active

Set TaxPerc = Range("B5") 'not active

If Not Application.Intersect(TaxPerc, Range(Target.Address)) _
Is Nothing Then
Dim inNumber, OutNumber As Double
Dim decPlaces As Integer
decPlaces = 2
inNumber = Range("D3").Value * Range("B5").Value
OutNumber = WorksheetFunction.RoundUp(inNumber, decPlaces)
Range("D5").Value = OutNumber

Else
Range("B5").Value = Range("D5").Value / Range("D3").Value

End If
End Sub

---

 

[that1guy]

Sorry... forgot this: (Office Professional Plus - Excel 2010, Windows 7)

 

[shg]

Maybe ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$5" Then
         Range("D5").Value = CCur(Range("D3").Value * Range("B5").Value)
    ElseIf Target.Address = "$D$5" Then
        If Range("D3").Value <> 0 Then
            Range("B5").Value = Range("D5").Value / Range("D3").Value
        End If
    End If
End Sub

 

[that1guy]

Thanks for the reply, shg! That does seem to be a more elegant way of writing the code I posted. But it looks like when I run the new code, the cells are behaving the same way. (including the weird 13 = 1300.000% thing) I've tried assigning the rounded value to an intermediate variable, and to a different cell thinking I might avoid the new value assigning itself back to the tax % cell if I reference these instead. But... to no avail.

 

[shg]

I don't understand the logic of what you're trying to do. If you don't want the code to write the tax percentage, remove it from the code. If you want the code to round it to 0.01%, do so. If you want to do something else, ...?

 

[that1guy]

Sorry, maybe my initial post was unclear. Let me try again...

My goal is to enter a value in the sales tax % cell. I then want to calculate the sales tax $ amount based on the sales tax % entered and the subtotal cell value. My hope is that I can have my code round the answer to this calculation (written to the sales tax $ cell) without affecting the value entered in the sales tax % cell. What I am experiencing now is that the rounding function is providing the expected sales tax $ value, but also re-writes the value entered by the user in the sales tax % cell.

Hopefully I did a better job that time.

Thanks, again for you help!

 

[shg]

The code only writes the sales tax % value if you change the sales tax amount value.

 

[that1guy]

That's what I thought too. But for some reason, if enter a value in the sales tax % cell that requires rounding, the value entered into the sales tax % cell changes. For example:

Subtotal ("D3") = $200.00
Sales Tax % ("B5") = 10.752%

(code runs)

Sales Tax % ("B5") = 10.755%
Sales Tax $ ("D5") = $21.51

I'm pretty sure the code isn't written explicitly to write a new value to ("B5"). But something about the way the code is rounding is causing the sales tax % value to change. My guess it has something to do with they way excel handles the rounding function. I was hoping there would be a way around this, but it very well might be impossible. I just can't quite figure out what it's doing.

 

[shg]

Ah. Brain cramp.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Oops
    Application.EnableEvents = False
    
    If Target.Address = "$B$5" Then
        Range("D5").Value = CCur(Range("D3").Value * Range("B5").Value)
    ElseIf Target.Address = "$D$5" Then
        If Range("D3").Value <> 0 Then
            Range("B5").Value = Range("D5").Value / Range("D3").Value
        End If
    End If
Oops:
    Application.EnableEvents = True
End Sub

 

[that1guy]

Oh, awesome! The overwriting issue is no more... yay! This is almost perfect... but now you've gone a bit beyond me and I'm finding myself a little lost in the newbiesville forest.

I just need the sales tax $ amount to always round up. (eg: $100.00 * 10.252% needs to = $10.26 instead of $10.25) But your code is a bit too cool for me to figure out how to make that adjustment.

Thanks, shg... you're saving me here!