Generate a series of random numbers based on input from a column. VBA Excel

Question

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.
For example if A4 = 3, then I want a random numbers in B4,C4 and D4.

I have the following code 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

Scott Craner · Accepted Answer · 2016-12-30 20:39:04Z

3

You have your loops switched:

Sub RandomNumbers()

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

    Next i
End With

End Sub

answered Dec 30, 2016 at 20:39

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Captain Grumpy · Accepted Answer · 2016-12-30 20:51:13Z

2

Sub RandomNumbers()
Dim i As Integer
Dim j As Integer
Dim lastrow As Integer
Dim iValue As Integer
Dim iColCount As Integer

j = 1
lastrow = Range("a1").End(xlDown).Row

For i = 1 To lastrow
    iValue = ThisWorkbook.Sheets("LossFrequency").Cells(i, j).Value
    If iValue > 0 Then
        For iColCount = 1 To iValue
            ThisWorkbook.Sheets("LossFrequency").Cells(i, iColCount + 1).Value = Int((300 - 200 + 1) * Rnd + 200)
        Next iColCount
    Else
        ThisWorkbook.Sheets("LossFrequency").Cells(i, j + 1).Value = 0
    End If

Next i

End Sub

answered Dec 30, 2016 at 20:51

Captain Grumpy

5205 silver badges9 bronze badges

Comments

user3598756 · Accepted Answer · 2016-12-31 09:14:58Z

2

here's a formula approach

Sub RandomNumbers()
    Dim cell As Range

    With ThisWorkbook.Sheets("LossFrequency")
        For Each cell In .Range("A1", .Cells(.Rows.count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers)
            If cell.Value = 0 Then
                cell.Offset(, 1).Value = 0
            Else
                cell.Offset(, 1).Resize(, cell.Value).FormulaR1C1 = "=RandBetween(300,200)"
            End If
        Next
    End With
End Sub

answered Dec 31, 2016 at 9:14

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Generate a series of random numbers based on input from a column. VBA Excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related