In message
s.com of Wed, 10 Aug 2011 08:01:53 in microsoft.public.excel.programmin
g, Paul Black writes
On Aug 3, 5:55*pm, "joeu2004" wrote:
"Paul Black" wrote:
I tried to run the program using only say 5 numbers
from 50 and exclude producing the second set of numbers
and it gave me a ...
Run-time error '9'
Subscript out of range
... I managed to get around this by commenting out ...
ReDim myLucky(1 To nFromLucky)
The problem with ReDim myLucky(1 To nFromLucky) is that nFromLucky is 0.
The upper bound of an array can't be less than its lower bound.
Off-hand, I cannot see any reason why the change would lead to that error or
why the work-around would correct it. *I suspect that there is still a
defect in your code, and it really is not working as you intend it to. *You
have just gotten lucky because of the macro's random nature.
If you can reproduce the "out of range" error, I suggest that you post
__that__ macro (the one that fails) so that we can help you correct the root
cause.
Hi joeu2004,
Thanks for the reply.
I would have answered earlier but the discussion group has only just
been updated.
I suspect you are using Google to access usenet.
If so, do yourself a favour and get a real news reader and leave Google
to what it does well. <
http://en.wikipedia.org/wiki/Newsreader_%28Usenet
%29 may help. I use the following to read and write news - both have
never simultaneously failed for me - free.teranews.com and
news.eternal-september.org.
Anyway, here is the code that produces the error ...
Option Explicit ' is a good thing!
Sub Random()
Dim nDrawnMain As Long
I infer you don't know about Const nDrawnMain As Long = 5
Dim nFromMain As Long
Dim nDrawnLucky As Long
Dim nFromLucky As Long
Dim nComb As Long
Dim myMain() As Variant
Dim myLucky() As Variant
Dim j As Long, h As Long, n As Long, k As Long ' Needed as _
Option Explicit used above.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
I would comment those 3 lines until you have software which does as you
intend. I see no reason for Application.DisplayAlerts = False. I suspect
you have copied and pasted it because another author used it. If you
click on DisplayAlerts in that line and press F1, Excel help should
allow you to understand that line, which is not needed in the code you
post.
nDrawnMain = 5
nFromMain = 50
nDrawnLucky = 0
nFromLucky = 0
nFromLucky = 5 ' = 1 for ReDim myLucky(1 To nFromLucky) below to work
Worksheets("Random Numbers").Select
With ActiveSheet
.Columns("A:K").ClearContents
ReDim myMain(1 To nFromMain)
ReDim myLucky(1 To nFromLucky)
nComb = .Range("N18").Value
Debug.Assert nComb = 1 ' Quick and dirty plausibility check.
End With
Randomize
For j = 1 To nComb
For h = 1 To nFromMain
myMain(h) = h
Next h
n = nFromMain
For k = 1 To nDrawnMain
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, k - 1) = myMain(h)
If h < n Then myMain(h) = myMain(n)
n = n - 1
Next k
For h = 1 To nFromLucky
myLucky(h) = h
Next
n = nFromLucky
For k = 1 To nDrawnLucky
h = Int(n * Rnd) + 1
Range("B2").Offset(j - 1, nDrawnMain + k) = myLucky(h)
If h < n Then myLucky(h) = myLucky(n)
n = n - 1
Next
Next j
Range("O18").Select
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Thanks.
Kind regards,
Paul
I suggest you step through your code in the debugger.
To get to that in Excel 2003, I do Alt+F8 (or Tools/Macro/Macros...),
select Random and click Step Into. You need simple instructions on
stepping through the code. I hope Google will find them for you.
I renamed "Sheet1" as "Random Numbers" and put 1 in N18.
I stepped through and found numbers were written to "B2:F2".
I infer you expect something to be written to "O18", but do not feel
like finding out what line you expect to do so - none do and I see none
likely to do so.
You have not said which version of Excel you use. The controls are quite
different in Excel 2007 and Excel 2010 and, probably, in Excel 2011 for
Mac.
After I started writing this,
suggested you originally
had nDrawnLucky = 2: nFromLucky = 9 and that with those restored, the
code works fine. It still does not write "O18", but does not crash.
HTH
--
Walter Briscoe