Quote Originally Posted by shg View Post
See if this works for you:
Function RandTot(iTot As Long, iLo As Long, iHi As Long, _
                 Optional bVol As Boolean = False) As Variant
    Dim nNum        As Long
    Dim i           As Long
    Dim ad()        As Double
    Dim iTry        As Long

    If bVol Then Application.Volatile
    With Application.Caller
        If .Rows.Count > 1 And .Columns.Count > 1 Then
            RandTot = "Enter as row or column vector only!"
            Exit Function
        End If
        nNum = .Count
    End With

    If iHi < iLo Or _
       iTot < nNum * iLo Or _
       iHi > iTot Then
        RandTot = CVErr(xlErrValue)
        Exit Function
    End If

    ReDim ad(1 To nNum)
    Randomize

    With WorksheetFunction
        Do
            iTry = iTry + 1
            If iTry > 200 Then
                RandTot = "Time-out"
                Exit Function
            End If

            For i = 1 To nNum - 1
                ad(i) = RandBetw(iLo, iHi)
            Next i
            ad(i) = iTot - .Sum(ad) + ad(i)
        Loop Until .Min(ad) >= iLo And .Max(ad) <= iHi
    End With

    RandTot = ad
End Function

Function RandBetw(iLo, iHi) As Long
    RandBetw = (Rnd * (iHi - iLo) + Rnd * (iHi - iLo) + Rnd * (iHi - iLo)) / 4 + iLo
End Function
Copy the code to a Code module (see below), then select A1:A10 and use the formula,

=TRANSPOSE(RandTot(100000, 5000, 20000))

The formula MUST be confirmed with Ctrl+Shift+Enter rather than just Enter.

Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel
Hello Sir,

I tried above code with below formula

=TRANSPOSE(RANDTOT(750,1,90))

it is giving me Time-Out. Is there a way out for this code to work for my criteria ? My requirement is sum to be 750 and numbers should be between 1 to 90.