Modifying a Randomised Alphabet Worksheet

[dpbarry]

Hi Folks.

I have a spreadsheet which randomised 29 'letters' - 'A to Z' plus 'Mac', 'Mc' and O'.

As it stands, it it works great but now our Principal wants the Mac, Mc and O' taken out. If I remove the relevant columns, this happens. I get spaces in the Randomised list

I've tried modifying the VB Code:

Option Explicit

Private Sub Shuffle_Click()
Dim Letters(1 To 29) As String
Dim Mask As String
Dim Counter, RandNumber As Integer

For Counter = 1 To 29
Letters(Counter) = Cells(3, Counter + 1)
Next

Mask = "ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]"
Randomize

For Counter = 1 To 29 'Randomise Letters Array Values
RandNumber = Int((Len(Mask) - 1 + 1) * Rnd + 1)
Cells(7, Counter + 1) = Letters(Asc(Mid(Mask, RandNumber, 1)) - 64)
Mask = Left(Mask, RandNumber - 1) & Right(Mask, Len(Mask) - RandNumber)
Next
End Sub

to:

Option Explicit

Private Sub Shuffle_Click()
Dim Letters(1 To 26) As String
Dim Mask As String
Dim Counter, RandNumber As Integer

For Counter = 1 To 26
Letters(Counter) = Cells(3, Counter + 1)
Next

Mask = "ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]"
Randomize

For Counter = 1 To 26 'Randomise Letters Array Values
RandNumber = Int((Len(Mask) - 1 + 1) * Rnd + 1)
Cells(7, Counter + 1) = Letters(Asc(Mid(Mask, RandNumber, 1)) - 64)
Mask = Left(Mask, RandNumber - 1) & Right(Mask, Len(Mask) - RandNumber)
Next
End Sub

but I end up with a error

and when I debug, ths is where the error occurs

I didn't write the code so I'm not sure what is happening and would appreciate any assistance as to how to rectify the error

Regards

Declan

 

[AlanY]

change

Mask = "ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]" to

Mask = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

should work

 

[KOKOSEK]

In second code remove last 3 signs from mask into:

Mask = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

and should be ok.

 

[dpbarry]

Thank You AlanY and KOKOSEK.

That did the job. Could you explain what removing [\] does to the code? Its just to get an understanding of what is happening.

Regards

 

[AlanY]

Thank You AlanY and KOKOSEK.

That did the job. Could you explain what removing [\] does to the code? Its just to get an understanding of what is happening.

Regards

you're welcome.

it just reduce the 26+3 to 26 letters

 

[KOKOSEK]

Ascii codes for [\] are 91,92 and 93, after minus 64 gives 27,28 and 29. In row:

Cells(7, Counter + 1) = Letters(Asc(Mid(Mask, RandNumber, 1)) - 64)

you want to put element 27,28 or 29 into cell but Letters array has only 26 elements.

For Counter = 1 To 26
Letters(Counter) = Cells(3, Counter + 1)
Next