Controlling the Decimal Places

EE Pros,

I have Two Worksheets in a Workbook (attached) that change Currency Formatting when a new Currency is selected.  A table controls the decimal points on the results.  I have some fields that require 2 Decimals and some that require -0- (i.e. numbers ranging from .15 to 150000000.  So you see the need for controlling the decimal points at the end result cell.

I'm hoping there is a simple way to do this but I have tried changing the Cell Formatting (which reverts to the table when the currency changes).  Any ideas?

Who is Participating?
Martin LissConnect With a Mentor Older than dirtCommented:
Try this where I added/modified lines 19 to 24.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFind As Range, rStep As Range
    Dim sSheet As String, sRange As String
    Dim strNF As String

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = Me.Range(SelectedCurrency).Address Then
        Set rFind = Me.Range(CurrencyList).Find(What:=Target.Value, LookAt:=xlWhole)
        If Not rFind Is Nothing Then
            For Each rStep In Me.Range(Me.Range(ChangeCellStart), Me.Cells(Me.Rows.Count, Me.Range(ChangeCellStart).Column).End(xlUp))
                On Error Resume Next
                If InStr(1, rStep.Value, "!") = 0 Then
                    Me.Range(rStep.Value).NumberFormat = rFind.Offset(0, 1).NumberFormat
                    sSheet = Trim(Left(rStep.Value, InStr(1, rStep.Value, "!") - 1))
                    sRange = Trim(Right(rStep.Value, Len(rStep.Value) - InStr(1, rStep.Value, "!")))
                    If Left(sSheet, 1) = "'" Then sSheet = Right(sSheet, Len(sSheet) - 1)
                    If Right(sSheet, 1) = "'" Then sSheet = Left(sSheet, Len(sSheet) - 1)
                    If sRange = "C9" Then
                        strNF = rFind.Offset(0, 1).NumberFormat
                        strNF = Replace(rFind.Offset(0, 1).NumberFormat, ".00", "")
                    End If
                    ThisWorkbook.Worksheets(sSheet).Range(sRange).NumberFormat = strNF
                End If
                On Error GoTo 0
            Next rStep
        End If
    End If
End Sub

Open in new window

Martin LissOlder than dirtCommented:
Sorry. I had a question here but I figured it out. Will get back to you.
Bright01Author Commented:
Sorry for the delay; Let me try this and get back with you in 24 hrs.  On the road....

Thank you,

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Martin LissOlder than dirtCommented:
You may still be unpacking but did you get a chance to test this?
Bright01Author Commented:

I am so sorry to have taken so long in testing this.  It worked perfectly!  I just got in to South Africa and it's the only excuse I have for not getting back with you sooner.  Thank you so much!  I may author a slightly different question about how to address the same requirement on a different "sheet".

Thanks again,

Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.