Generate a random number from an input range of values, some of which are empty cells

user_19325340 0
Viewed 51

I am trying to get VBA to pick a random value from a range of values and output this value in Cell E6. Some of the cells within this input range of values are blank so I need to tell VBA to pick it from a cell that contains a value. I really am unsure how to do this. I will copy what I have below. The range of which the values to choose from is H127:1127 (a lot of which are empty)

Sub Generate()
    Dim i As Double
    Dim ws As Worksheet
    
    Set ws = Sheets("Upstream-Overall")
    
        For Each Cell In ws.Range("H127:H1127")
            If ActiveCell.Value <> "" Then
                Range("E6") = Random_Number = Application.WorksheetFunction.RandBetween(0.1, 5)
            End If
        Next Cell
    
End Sub
random vba excel
2 Answers

Return the Number From a Random Cell

Sub Generate()
    
    Const wsName As String = "Upstream-Overall"
    Const sRangeAddress As String = "H127:H1127"
    Const dCellAddress As String = "E6"
    
    ' Reference the worksheet in the workbook containing this code.
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets(wsName)
    
    ' Write the values from the first column of the range to an array.
    Dim Data As Variant: Data = ws.Range(sRangeAddress).Columns(1).Value
    
    Dim sr As Long, dr As Long
    
    ' Shift the numeric values (up) to the beginning of the array.
    For sr = 1 To UBound(Data, 1)
        If VarType(Data(sr, 1)) = 5 Then ' is a number
            dr = dr + 1
            Data(dr, 1) = Data(sr, 1)
        'Else ' is not a number; do nothing
        End If
    Next sr
    
    ' Check if at least one number was found.
    If dr = 0 Then
        MsgBox "No numbers in the first column of the range.", vbCritical
        Exit Sub
    End If
    
    ' Write the number from a random element
    ' of the numeric part of the array to the cell.
    ws.Range(dCellAddress).Value = Data(Int(dr * Rnd + 1), 1)
        
    ' Inform of success.
    MsgBox "New random number generated.", vbInformation
        
End Sub
user_98140690

You could do something like this:

Sub Generate()
    Dim ws As Worksheet, rng As Range, i As Long, v
    
    Set ws = Sheets("Upstream-Overall")
    Set rng = ws.Range("H127:H1127")
    Do
        i = Application.RandBetween(1, rng.Cells.Count)
        v = rng.Cells(i).Value
    Loop While Len(v) = 0  'loop until selected cell has a value
    
    ws.Range("E6").Value = v
     
End Sub

(assuming the range will never be completely empty)

user_4788840
Related