VBA to randomly assign values to a cell

[andrewb90]

Hello,

I am trying to make a macro that when clicked, will randomly choose one of 7 values and type that into cell C4. Values can be Dodgeball, Football, Soccer, Tetherball, Soccer, Baseball, Basketball. Is this doable?

Thanks!

Andrew

 

[shg]

One way:

Sub x()
    ActiveCell.Value = [index({"Dodgeball", "Football", "Soccer", "Tetherball", "Soccer", "Baseball", "Basketball"}, randbetween(1,7))]
End Sub

 

[andrewb90]

This works very well, however I was wondering if It could be changed to a specific cell instead of the active cell?

 

[shg]

Range("A1").Value = ...

 

[andrewb90]

great! thank you!

 

[shg]

You're welcome.

 

[andrewb90]

Quick question. I added a few more values and in in VBA I get a Compile Error: Identifier too long. (I changed the value at the end to 14 as well) Is there something that would cause this problem?

 

[shg]

Code?

 

[andrewb90]

Sub bottom()    Range("H5").Value = [index({"Basket 2 on two", "Foot 3 on 3", "Base 4 on 4", "Basket 2 on two", "Tether 1 on 1", "Soccer 3 on 3 or 1 on 1", "Golf 1 on 1", "Frisbee 4 on 4 or 5 on 5", "Curl anybody's game", "Shuffle 8x1", "Volley 2 on 2 or 3 on 3", "Crick 1 on 1", "Bad 1 on 1", "Tennis 2 on 2"}, randbetween(1,14))]
End Sub

 

[shg]

Sub bottom()
    Dim asSport()   As String

    asSport = Split("Basket 2 on two|Foot 3 on 3|Base 4 on 4|Basket 2 on two|" & _
                    "Tether 1 on 1|Soccer 3 on 3 or 1 on 1|Golf 1 on 1|" & _
                    "Frisbee 4 on 4 or 5 on 5|Curl anybody's game|Shuffle 8x1|" & _
                    "Volley 2 on 2 or 3 on 3|Crick 1 on 1|Bad 1 on 1|Tennis 2 on 2", "|")
    Range("H5").Value = asSport(Int(Rnd() * UBound(asSport)) + 1)
End Sub