Random number generator button

[TessieBear99]

I need to be able to generate a random number between 1 and x, with x being the number of people listed in my worksheet. The formula I have to do this works, it is:

=RAND()*(COUNTIF(B2:B198,"<>"&"")-1)+1

Now I need to create a macro to assign to my button so that when the button is clicked, that formula runs and the value is pasted into E1. I've tried this with the following code but when I run it, it comes out with "TRUE" in E1 instead of the value.

Sub RandomNumberGenerator()

'Generates random number between 1 and total number of names listed

    Range("E1").Formula = "=RAND()*(COUNTIF(B2:B198," <> " & "")-1)+1"
    Range("E1").Copy
    Range("E1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub

Can someone please assist?

 

[Georgiboy]

Maybe the below will do it?

Sub test()
    With Application
        Range("E1") = .RandBetween(1, .CountA(Range("B2:B198")))
    End With
End Sub

Or another option:

Sub test2()
    Range("E1") = Evaluate("RANDBETWEEN(1," & Application.CountA(Range("B2:B198")) & ")")
End Sub

 

[TessieBear99]

Thank you! The first one works perfectly

 

[Georgiboy]

You are welcome, thanks for the feedback.