Generate random numbers between two limits and fixed value in vba

maxValue is 108. 20 108 = 2160 so if all your values end up being 108 in the first 19 cells (improbable, but certainly possible), then your last cell will have to be 2100 - 19 108 = 2100 - 2052 = 48, which would be bad.

If all your values end up being 100 in the first 19 cells, another improbable, but possible scenario, then your last cell will be 2100 - 1900 = 200. Again, unacceptable.

The solution is to always adjust the minimal and maximum value of your randomization to ensure that your value are still acceptable. If your total needs to be 2100, then its 5% = 105, so if all your cells are 105, then their sum is correct. Hence, it would make sense to say that maxValue is ~= total / (20 - i + 1) + diff and minValue is ~= total / (20 - i + 1) - diff. Of course, in each step, total will be smaller than before and i incremented This way the randomized value will stay close to the average of the current total at each step. Try this approach, maybe it's already good-enough, but if not, then you can adjust it. If you need to keep it always between 100 and 110, for example and you randomized some values so far, then after each randomization see whether the other cells could still be in the interval you expect and if your current random would force the other cells to move out from the threshold somewhere, then adjust the randomization of your current cell with adjusted bounds that will force it to have a result that's acceptable from the perspective of the subsequent cells.

So if you ended up with 100 for the first 10 cells, then the next 10 cells must all be 110.

If the first 10 cells were all 100, then the next cell may be anything between 100 and 110, as the bounds still allow that. To find out whether you could get out of bounds by the current randomization, you simply need to check whether total - 100 as well as total - 110 is in the interval of [(20 - i) 100, (20 - i) 110]. If so, then your current value could be anything between 100 and 110. If not, then either total - 100 is too big, or total - 110 is too small. You can get the amount of undesired deviation:

• if total - 100 > (20 - i) 110, then adjust maxValue in the current randomization to 110 - ((total - 100) - ((20 - i) 110))
• if total - 110 < (20 - i) 100, then adjust maxValue in the current randomization to 100 + (((20 - i) 100) - (total - 110))

EDIT

POC in Javascript

let total = 2100;
let minValue = 100;
let maxValue = 110;
let cells = [];
let sum = 0;
for (let i = 1; i  total) {
            currentMaxValue = 110 - (sum + 110 + remainingSteps  100 - total);
        }
        let currentCellValue = parseInt(Math.random()  (currentMaxValue + 1 - currentMinValue) ) + currentMinValue;
        cells.push(currentCellValue);
        sum += currentCellValue;
    }
}
console.log({sum, cells});

The above is an implementation in Javascript. Basically you'll need the same in VBA, but I did not work with VB for a decade. Nevertheless, you can simply write the VBA equivalent of this and then you won't need to randomize "until it's good".

I have just added a single line code before End Sub and my problem is solved.

If Abs(Range("A21").Value - Range("A22").Value) > 8 Then Call Rand_Num

Note: Range("A21") is 19th cell and Range("A22") is 20th cell.

Yaahoooooooo...!

Sub Rand_Num1()
    Dim total As Double
    Dim numCells As Integer
    Dim minValue As Double
    Dim maxValue As Double
    Dim rng As Range
    Dim nr As Long
    Dim i As Long
    Dim res() As Long
' Define parameters
    total = 2100
    numCells = 20
    minValue = 100
    maxValue = 108
    ReDim res(1 To numCells, 1 To 1)
    For i = 1 To numCells
        res(i, 1) = minValue
    Next i
    Set rng = Worksheets(1).Range("A3:A" & numCells + 2)
    rng.ClearContents
    Randomize ' Initialize the random number generator
    ' Choose random cell and add 1
    For i = 1 To total - minValue * numCells
        nr = WorksheetFunction.RandBetween(1, numCells)
        If res(nr, 1) < maxValue Then
            res(nr, 1) = res(nr, 1) + 1
        Else
            i = i - 1   ' repeat
        End If
    Next i
    rng.Value = res
End Sub