RefEdit in UDF ?
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 |
RefEdit in UDF ?
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" 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 |
RefEdit in UDF ?
Chip,
thank you very much ! (And your website is a treasure, hopefully to be continued...) Kind regards, hgl "Chip Pearson" wrote in message ... 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" 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 |
RefEdit in UDF ?
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 |
RefEdit in UDF ?
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 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 |
RefEdit in UDF ?
Thank you for the correction, Chip!
hgl "Chip Pearson" wrote in message ... 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 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 |
All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com