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

---

 

[shg]

Range("D5").Value2 = worksheetfunction.ceiling(Range("D3").Value2 * Range("B5").Value2, 0.01)

 

[that1guy]

That rocks! You're the best, shg. Thanks for you patience and perseverance working on my little vba puzzle. Your codes were much more effective than my usual attempts to fix things. hammer Hopefully someday I will know enough to return the favor... until then, feel free to contact me if you need anything noob-i-fied!

 

[shg]

You're welcome.

The brain-cramp problem was that the code changing the cells retriggers the change event. Just wasn't paying attention.