CODEWITHSUNDEEP
vbaexcel
doyz
doyz

Reputation: 886

Excel VBA: Trim decimal places off a percentage

Below is a snippet of an excel vba code. I'm trying to combine a percentage value and numerical sample size value (both in separate cells) into one cell

However, there are many decimals for the % value. Even though I have changed the format to show only 1 decimal value, when I click on the cell, it still shows multiple decimal places. Hence, when I combine the 2 variables, the full length of decimal places is combined with the sample size, which is not what I want.

How do I trim off these unnecessary decimal places?

        For j = Row_SourceStart To Row_SourceEnd

            wbSource.Activate
            wbSource_ACX.Select
            Percentage_value = wbSource_ACX.Range("M" & j).NumberFormat = "0.0%"
            Sample_size = wbSource_ACX.Cells(j, 12)

            wbTarget.Activate
            Set wbTarget_ACX = wbTarget.Worksheets(ArrayWS_Source(0))
            wbTarget_ACX.Select
            wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Percentage_value & " (" & Sample_size & ")"

        Next j

Upvotes: 0

Views: 1954

Answers (2)

FunThomas
FunThomas

Reputation: 29171

In VBA, you can use the round function.

wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Round(Percentage_value, 1) & " (" & Sample_size & ")"

If your percentage value is already correctly formatted in the sheet, use the text-property of the cell:

wbTarget_ACX.Cells(Row_TargetStart, j + 5) = wbSource_ACX.Range("M" & j).text & " (" & Sample_size & ")"

Else calculate the value by your own (and add the %-sign)

Percentage_value = wbSource_ACX.Range("M" & j).Value * 100
wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Round(Percentage_value, 1) & "% (" & Sample_size & ")"

BTW: You can (and should) remove all the activate and select commands - they are not neccessary.

Upvotes: 0

rohrl77
rohrl77

Reputation: 3337

The value in the cell is only formated to look like a rounded percentage. Excel stores the actual value as calculated with all decimal places in the background. You see this when you click on the cell and look at the value in the formula bar.

You can trim the value to 1 decimal place in VBA either by a number of methods. There is the Round Function in VBA or you can use one of the worksheet functions that also do rounding. Here are a few examples (with the number in the example being written in cell A1 of the active sheet):

Sub test()
    Debug.Print Round(Range("a1"), 1)
    Debug.Print WorksheetFunction.Round(Range("a1"), 1)
    Debug.Print WorksheetFunction.RoundUp(Range("a1"), 1)
    Debug.Print WorksheetFunction.RoundDown(Range("a1"), 1)
End Sub

Upvotes: 0

Related Questions