CODEWITHSUNDEEP
excelvba
KjBox
KjBox

Reputation: 65

Format a number that can be variably rounded with thousand separator

I have code that ends up with a variable "dTot" which will be a number with up to 5 decimal places.

The user has an option to set the number of decimal places for rounding dTot, user selection is assigned to a variable "iDP", this can be anything between 0 and 5.

dTot = Round(dTot, iDP)` works just fine.

But, I also need to format the rounded number with thousand separator when adding to to another array.

Format(Round(dTot, iDP), "#,##0.00000")

works if iDP is 5, and

Format(Round(dTot, iDP), "#,##0.00")

if iDP is 2

How can I get the format to follow the iDP value?

I know I could use "Select Case iDP" and have 6 Cases, but since the formatting needs to be applied to up 6 expressions in each of 200 rows in the array I was hoping to find a way to avoid "Select Case"

Upvotes: 1

Views: 143

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

EDIT: Added the brilliant '& IIF(iDP, ".", "")' used in the accepted answer by user10825204:

WorksheetFunction.Rept vs Replace(Space)

WorksheetFunction.Rept

Format(Round(dTot, iDp), "#,##0" _
    & IIF(iDP, ".", "") & WorksheetFunction.Rept("0", iDp))

Replace(Space)

Format(Round(dTot, iDp), "#,##0" _
    & IIF(iDP, ".", "") & Replace(Space(iDp), " ", "0"))

Upvotes: 1

user10825204
user10825204

Reputation:

Build the format mask string as needed.

Format(Round(dTot, iDP), "#,##0" & IIF(iDP, ".", "") & string(iDP, "0"))

enter image description here

Upvotes: 3

Related Questions