Prev: Need a way to verify a string is present in 1-column array?
Next: Run Time Error on trying to save workbook
From: H.G. Lamy on 2 Feb 2010 17:22
Hello,

I often use this simple UDF:

Public Function RANDNAME(ElementList)
Application.Volatile (True)
RANDNAME = WorksheetFunction.Index(ElementList, (Rnd() *
WorksheetFunction.CountA(ElementList)) + 1)
End Function

which provides a random text element from a list (named range). It works
fine.

However, I always have to manually type the name of the element list (named
range) into the function's argument panel.

I would much prefer to point to a cell address which already contains that
name.

How could I possibly adapt the above UDF, so that a cell with the element
list's name would suffice ?

Thank you in advance.

Kind regards,

H.G. Lamy





From: Chip Pearson on 2 Feb 2010 18:11

Try something like

Function RandName(ElementList As String) As String
Dim N As Long
Dim R As Range
Set R = Range(ElementList)
N = R.Cells.Count
N = Int(N) * Rnd + 1
RandName = R(N)
End Function

Then, define a name of "TheName" (no quotes) to refer to A1:A10. Then,
in cell C1, enter "TheName" (without the quotes). Finally, call the
function with

=RandName(C1)

The function will return a random value from the list that is
referenced either by name or address in C1. So, if C1 = "TheName" (no
quotes), the function will look in the range TheName and retrieve a
value from that range. Similarly, C1 could contain "A1:A100" and the
function would return a value from A1:A100.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Tue, 2 Feb 2010 23:22:48 +0100, "H.G. Lamy" <Enterplan(a)web.de>
wrote:

>Hello,
>
>I often use this simple UDF:
>
>Public Function RANDNAME(ElementList)
> Application.Volatile (True)
> RANDNAME = WorksheetFunction.Index(ElementList, (Rnd() *
>WorksheetFunction.CountA(ElementList)) + 1)
>End Function
>
>which provides a random text element from a list (named range). It works
>fine.
>
>However, I always have to manually type the name of the element list (named
>range) into the function's argument panel.
>
>I would much prefer to point to a cell address which already contains that
>name.
>
>How could I possibly adapt the above UDF, so that a cell with the element
>list's name would suffice ?
>
>Thank you in advance.
>
>Kind regards,
>
>H.G. Lamy
>
>
>
>
From: H.G. Lamy on 2 Feb 2010 18:34
Chip,

thank you very much !
(And your website is a treasure, hopefully to be continued...)

Kind regards,

hgl

"Chip Pearson" <chip(a)cpearson.com> wrote in message
news:j0chm5109j8i7g2ftht52thcj1t2ammlr8(a)4ax.com...
>
> Try something like
>
> Function RandName(ElementList As String) As String
> Dim N As Long
> Dim R As Range
> Set R = Range(ElementList)
> N = R.Cells.Count
> N = Int(N) * Rnd + 1
> RandName = R(N)
> End Function
>
> Then, define a name of "TheName" (no quotes) to refer to A1:A10. Then,
> in cell C1, enter "TheName" (without the quotes). Finally, call the
> function with
>
> =RandName(C1)
>
> The function will return a random value from the list that is
> referenced either by name or address in C1. So, if C1 = "TheName" (no
> quotes), the function will look in the range TheName and retrieve a
> value from that range. Similarly, C1 could contain "A1:A100" and the
> function would return a value from A1:A100.
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
>
>
>
>
>
> On Tue, 2 Feb 2010 23:22:48 +0100, "H.G. Lamy" <Enterplan(a)web.de>
> wrote:
>
>>Hello,
>>
>>I often use this simple UDF:
>>
>>Public Function RANDNAME(ElementList)
>> Application.Volatile (True)
>> RANDNAME = WorksheetFunction.Index(ElementList, (Rnd() *
>>WorksheetFunction.CountA(ElementList)) + 1)
>>End Function
>>
>>which provides a random text element from a list (named range). It works
>>fine.
>>
>>However, I always have to manually type the name of the element list
>>(named
>>range) into the function's argument panel.
>>
>>I would much prefer to point to a cell address which already contains that
>>name.
>>
>>How could I possibly adapt the above UDF, so that a cell with the element
>>list's name would suffice ?
>>
>>Thank you in advance.
>>
>>Kind regards,
>>
>>H.G. Lamy
>>
>>
>>
>>


From: Greg Glynn on 2 Feb 2010 22:26
This kind-of works and kind-of doesn't.

I have an 8 element test array and the code regularly generates 9 as
its random index number.

Greg
From: Chip Pearson on 3 Feb 2010 08:14

The line that generates the random index is bad. Change

N = Int(N) * Rnd + 1

To

N = ((Int(R.Cells.Count) - 1) * Rnd) + 1

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Tue, 2 Feb 2010 19:26:47 -0800 (PST), Greg Glynn
<gregoryglynn(a)gmail.com> wrote:

>This kind-of works and kind-of doesn't.
>
>I have an 8 element test array and the code regularly generates 9 as
>its random index number.
>
>Greg
 |  Next  |  Last
Pages: 1 2
Prev: Need a way to verify a string is present in 1-column array?
Next: Run Time Error on trying to save workbook