vendredi 30 décembre 2016

I want to generate a series of random numbers based on input from a column in vba excel

Column A has numbers from 0 to 5. When there is a number greater than 0, i want it to generate that number of random numbers in the columns next to that cell. so, for example a4 is 3 then I want a random numbers in b4,c4 and d4

I have the following that works fine in picking up values over 0 and generating a random number between 200 and 300 but I am stuck on how to have it generate more than one. can anyone point me in the right direction? thank you

Sub RandomNumbers()

Dim i As Integer
Dim j As Integer
Dim lastrow As Integer
lastrow = Range("a1").End(xlDown).Row
For j = 1 To 1
    For i = 1 To lastrow
        If ThisWorkbook.Sheets("LossFrequency").Cells(i, j).Value > 0 Then
            ThisWorkbook.Sheets("LossFrequency").Cells(i, j + 1).Value = Int((300 - 200 + 1) * Rnd + 200)
            Else: ThisWorkbook.Sheets("LossFrequency").Cells(i, j + 1).Value = 0
        End If

    Next i
Next j

End Sub




Aucun commentaire:

Enregistrer un commentaire