combine a formula with VBA to generate random numbers

[MattLevand]

I'm generating a random number in Cell AI5 between 2.5 and 6.0 if data is entered in Cell X5 using the formula =IF(X5>1,2.5+3.5*RAND(),""). Then i drag this down a spread sheet 365 cells for the entire year. Instead of changing the cell to a string everytime it generates a number (to keep the number from changing) couldn't i call a macro that generates my random number only if data is entered in Cell X5? (Cell X5 is always a whole number if it is entered) Or is there any other better way...
Thanks,

Matt

 

[Snakehips]

@MattLevand Welcome to MrExcel
I'm assuming you generate 356 new numbers each time X5 changes?
If so then try this code in the Worksheet code pane of the vba editor.
It will automatically react to changes in cell X5

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("X5")) Is Nothing And Target > 1 Then
With Range("A15:A379")
.Formula = "=IF($X$5>1,2.5+3.5*RAND(),"""")"
.Value = .Value
End With
End If


End Sub

Hope that helps.

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("X5:X370")) Is Nothing Then
      Target.Offset(, 11).Value = Evaluate("if(" & Target.Address & ">1,2.5+3.5*rand(),"""")")
   End If
End Sub