|[Ben]:||Excel Random Selection update ||Discuss This [0 comments so far] View Comments|
|Today there was a note waiting for me from an anonymous reader who was having trouble with a macro I made available a few years ago. |
I wanted to use your macro on this link:
http://www.benswenson.com/news.php?ctrl=viewNews&objid=1673. But when trying to run the macro I get a Random Select Macro - Compile Error: Sub or Function not defined Error. Could you perhaps point me in the right direction for getting the macro to work under Excel 2010?
I'd reply directly to you, but you didn't leave any contact info, so here's the solution.
To get this macro to work, you need to add the following function:
|Public Function GetColumnLetter(colNum As Long) As String|
Dim i, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then
GetColumnLetter = GetColumnLetter & Chr(((colNum - x - 1) 26 ^ i) Mod 26 + 65)
Excel Random Selection update