VBA: Set Decimal Places from Input Value

[sonofamonkey10]

Hello, I am working with a macro that take three input values (min, max, decimal places) and returns random numbers within those bounds to a selected area.

Sub RandNums()
'http://excel.tips.net/T002077_Generating_Random_Testing_Data.html
'creates random numbers within selection
'constraints on random numbers are defined by user by Min and Max values
 
    Dim MyRange As Range
    Dim lMin As Long, lMax As Long, decpnt As Long
    Dim dRand As Double
    ' If selection is not Excel Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set MyRange = Selection
    ' Get Min and Max value
    lMin = CLng(InputBox("Minimum?"))
    lMax = CLng(InputBox("Maximum?"))
    decpnt = CLng(InputBox("Decimal Places?"))
    Randomize
    Application.ScreenUpdating = False
    For Each c In MyRange.Cells
        ' Calculate random value, where
        ' Value >= Min And Value <= Max
        dRand = Round(Rnd * (lMax - lMin) + lMin, decpnt)
        ' Use the following line only if the random
        ' value should be an integer
        ' dRand = Int(dRand)
 
        c.Value = dRand
    Next c
    Application.ScreenUpdating = True
End Sub

I can get the code to work almost perfectly, except for how the number are displayed. For example, if I use min = 0, max = 20 and decpnt = 3, I will get most numbers to come out as #.000, but some as #.00, #.0 or #. Example data:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64 align=right>13.902</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>12.233</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>4.084</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16.748</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>7.856</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>5.98</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>1.011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>16.459</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>2.243</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>7.2</TD></TR></TBODY></TABLE>

So, the sixth and tenth numbers should be 5.980 and 7.200, but the output is leaving off insignificant digits.

How can I fix this code so that all output has the specified number of decimal places? I've played around with some Format codes, but nothing has worked properly.

-Thanks in advance.

 

[shg]

Welcome to the board.

Try adding this :

    Application.ScreenUpdating = True
[COLOR=red]    MyRange.NumberFormat = "0." & String(decpnt, "0")[/COLOR]

 

[sonofamonkey10]

Looks like that works. So far, so good.

Thanks a bunch!

 

[shg]

You're welcome, good luck.