Excel VBA code cannot catch up with randbetween function

Question

I'm VBA newbie and I got this problem: I'm trying to code something that compares the value in column A and returns a feedback in column B. Eg, for randbetween value 1 to 100,if cell value<50 type disqualify in Column B. Using if function can perfectly correspond but below code can only capture the result prior to running the code. Since every time when I run the code the numbers in column A will change automatically how can I fix the code so it can show the correct result?

 Sub tt()
 Dim x As Integer
 Dim rg As range
 For x = 1 To 11
 For Each rg In range("a" & x)
 If rg.Value < 50 Then
 range("b" & x) = "disqualify"
 End If
 Next rg, x
 
 End Sub

Answer 1

I would suggest one of these solutions:

Use fixed values in Column A

Instead of having a random formula in Column A, you could just have numbers instead. The code would then work as expected.

Use a formula

This is the preferred solution for this task. The formula would account for the changing numbers in column A. An example formula in B1 would be:

=IF(A1<50,"DISQUALIFY","ACCEPT")

Answer 2

Close to your code. You can switch off Application.Calculation = xlCalculationManual to prevent randbetween auto recalculations (then recalculate it with F9 if need; to switch on auto recalculations use Application.Calculation = xlCalculationAutomatic). You can also add Else Range("b" & x) = "" if rg.Value >= 50 to handle all values:

Sub tt()
    Dim x As Integer
    Dim rg As Range
    Application.Calculation = xlCalculationManual
    For x = 1 To 11
        For Each rg In Range("a" & x)
            If rg.Value < 50 Then
                Range("b" & x) = "disqualify"
            Else
                Range("b" & x) = ""
            End If
    Next rg, x
End Sub

Answer 3

Evaluate Random Values

• Generate the random values with the code.

Option Explicit

Sub tt()
    
    Const wsName As String = "Sheet1"
    ' Source        
    Const sFirst As String = "A1"
    Const sFormula As String = "=RANDBETWEEN(1,100)"
    Const sCriteria As String = "<50"
    ' Destination
    Const dCol As String = "B"
    Const dTrue As String = "disqualify"
    Const dFalse As String = ""
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    ' Create a reference to the Source Column Range ('srg').
    Dim sfCell As Range: Set sfCell = ws.Range(sFirst)
    Dim slCell As Range
    Set slCell = sfCell.Resize(ws.Rows.Count - sfCell.Row + 1) _
        .Find("*", , xlFormulas, , , xlPrevious)
    If slCell Is Nothing Then Exit Sub
    Dim srg As Range: Set srg = sfCell.Resize(slCell.Row - sfCell.Row + 1)
    Debug.Print srg.Address(0, 0), sFormula
    
    ' Write the Destination Formula to a variable ('dFormula').
    Dim dFormula As String
    dFormula = "=IF('" & wsName _
        & "'!" & sFirst _
        & sCriteria _
        & ",""" & dTrue _
        & """,""" & dFalse _
        & """)"
    
    ' Create a reference to the Destination Column Range ('drg').
    Dim drg As Range: Set drg = srg.EntireRow.Columns(dCol)
    Debug.Print drg.Address(0, 0), dFormula
    
    Application.ScreenUpdating = False
    
    ' Generate random values in the Source Column Range.
    With srg
        .Formula = sFormula
        .Value = .Value
    End With
    
    ' Write values to the Destintation Column Range.
    With drg
        .Formula = dFormula
        .Value = .Value
    End With

    Application.ScreenUpdating = True

End Sub