Generate random values between two number and specific string

Hello everyone
I know how to rand between two numbers but I need to generate along side by numbers specific strings say "A" & "B" & "C"
For example : rand between 10,100 and "A" And "B" And "C"
I need to create UDF for that if possible as I intend to use it several times
Thanks advanced for any help

I have posted it at this link
http://www.vbaexpress.com/forum/show...pecific-string

Don't quite understand, but maybe:
str = randbetween(1,100) & index(StringArray,randbetween(1,3))

Thanks a lot for reply
Suppose I have range("A1:A10") and I need to randomize data in this range .. The numbers are between 10,35 so in random
A1 = 15
A2=33
A3="C"
A4=12
A5="A" (without quotes)
A6=15
A7=30
A8="A"
A9=31
A10=29

Maybe you can use randbetween(1,2) to choose one of two other randbetween formulas, one for numbers and one for letters:


=CHOOSE(RANDBETWEEN(1,2),RANDBETWEEN(1,100),INDEX({"A","B","C"},RANDBETWEEN(1,3)))

Thanks a lot for this great formula
in fact my issue is more comlicated a little as the strings needed to be random is more than 15 strings so I need a udf if possible
I imagine myUDF(startnum,endnum,array of strings)

Maybe like:

Option Explicit
Public Function myUDF(ByVal startnum As Long, ByVal EndNum As Long, ListAddr) As String
    Randomize
    Dim StringList As Variant
    Dim ArrayRows As Long
    StringList = ListAddr
    ArrayRows = UBound(StringList)
    myUDF = ArrayRows
    
    Select Case Rnd() Mod 2
        Case Is = 1
            myUDF = WorksheetFunction.RandBetween(startnum, EndNum)
        Case Is = 0
        k = WorksheetFunction.RandBetween(1, ArrayRows)
            myUDF = StringList(k, 1)
    End Select
End Function

That's really wonderful and awesome
How can I use it in a code. I tried the following but got an error

Sub TestFunction()
    Dim cel As Range
    
    For Each cel In Range("F1:F10")
        cel.Value = myUDF(20, 30, Array("A", "B", "C"))
    Next cel
End Sub

That's really wonderful and awesome
How can I use it in a code. I tried the following but got an error

Sub TestFunction()
    Dim cel As Range
    
    For Each cel In Range("F1:F10")
        cel.Value = myUDF(20, 30, Array("A", "B", "C"))
    Next cel
End Sub

I put Option Base 1 clause at the top of the module and edit this line

myUDF = StringList(k)

Am I right? or there is a better way to have it flexible with both 1D and 2D arrays?

There are several ways:
Called from a cell:
1)
=myUDF($A$1,$B$1,D1:D10)

or 2)
=myUDF($A$1,$B$1,{"A","B","C","D","E"})

From a VBA Sub:
1)
Sub test()
TESTARR = Array("a", "b", "C", "d")
Z= myUDF(5, 8, TESTARR )
End Sub

2)
Sub test()
Z = myUDF(5, 8, Array("a", "b", "C", "d"))
End Sub

3)
Sub test()
testarr = Array("a", "b", "C", "d")
Z = myUDF(Sheets("sheet1").Range("A1").Value, Sheets("sheet1").Range("b1").Value, testarr)
End Sub

I tested the myUDF with one and two dim arrays. Seems to work ok with each with below mods.

---

Modified:

Public Function myUDF(ByVal startnum As Long, ByVal EndNum As Long, ListAddr) As String
    Randomize
    Dim StringList As Variant
    Dim ArrayRows As Long
    StringList = ListAddr
    ArrayRows = UBound(StringList)
    Lwrbnd = LBound(StringList)
    
    Select Case Rnd() Mod 2
        Case Is = 1
            myUDF = WorksheetFunction.RandBetween(startnum, EndNum)
        Case Is = 0
            k = WorksheetFunction.RandBetween(Lwrbnd, ArrayRows)
            On Error Resume Next
            myUDF = StringList(k, 1)
            If Err.Number > 0 Then
                Err.Clear
                myUDF = StringList(k)
            End If
    End Select
End Function

Hello Mr. protonLeah
That's fascinating and very awesome solution. I like it a lot as it is now more flexible
Thanks a lot for sharing us this great UDF
Regards