Random Letters

[mdorey]

hello friends...

I'm here to ask for you help to do a sheet with random lettes...

I got this macro but is not working

Sub RandomLetters()
Dim RandomRange As Range, cell As Range
Set RandomRange = Range("C10:AG15")
For Each cell In RandomRange
cell.Formula = "=RANDL(A,M)"
Next
RandomRange.Value = RandomRange.Value
End Sub

The range is set. but the cell.formula is not... i want to set the macro to random the letters (N ,M, T, E, DS) without their being repeated at the same collun is that possible???

TY

 

[mdorey]

First, I'd like to point out that the range C10:AG15 has 6 cells in each column whereas you said you wanted to fill the range with 5 non-repeating "letters" (N, M, T, E, DS) without repeats... that is kind of hard to do if you want to fill every cell. I'll assume you accidentally omitted a comma between the D and S. Give this macro a try...

Sub DistributeRandomLettersNoRepeatsInColumns()
  Dim X As Long, RandomIndex As Long, Col As Range, TempElement As String, Arr() As String
  Arr = Split("N M T E D S")  ' Single space delimited list of letters
  For Each Col In Range("C10:AG15").Columns
    For X = UBound(Arr) To 0 Step -1
      RandomIndex = Int((X - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(X)
      Arr(X) = TempElement
    Next
    Col = WorksheetFunction.Transpose(Arr)
  Next
End Sub

Hey Rick...

Your code works at 100% :D thats awesome Thanks so much...

I want this to do like a work time sheet and it's working hehehehe

I only change the Arr = Split("N M T E D S") to Arr = Split("N M T E Ds dS") because i really need to have 2 types of 'ds...

i just wanna ask you :P if it is possible to paint the cells were the 'ds' are automatically??? if it is possible just tell me how and i'll put the color code..

:D thanks

 

[wigi]

Just to point out, the WorksheetFunction does not support RandBetween in XL2003 because it is part of the "Analysis ToolPak" add-in.

That's true, and this slight change will work too:

sLetter = Mid(sChars, Int(Rnd * Len(sChars) + 1), 1)

 

[mdorey]

One more thing.... is possible to block the amount of times for 8 or 9 that the 'ds' appears per line???

 

[Rick Rothstein]

i just wanna ask you :P if it is possible to paint the cells were the 'ds' are automatically??? if it is possible just tell me how and i'll put the color code.

Give this modification to the code I posted a try...

Sub DistributeRandomLettersNoRepeatsInColumns()
  Dim X As Long, RandomIndex As Long, col As Range, TempElement As String, Arr() As String
  Arr = Split("N M T E Ds dS")  ' Single space delimited list of letters
  For Each col In Range("C10:AG15").Columns
    For X = UBound(Arr) To 0 Step -1
      RandomIndex = Int((X - LBound(Arr) + 1) * Rnd + LBound(Arr))
      TempElement = Arr(RandomIndex)
      Arr(RandomIndex) = Arr(X)
      Arr(X) = TempElement
    Next
    col = WorksheetFunction.Transpose(Arr)
  Next
  With Range("C10:AG15")
    .Interior.ColorIndex = xlColorIndexNone
    .Replace "Ds", "=Ds", xlWhole, , True
    .SpecialCells(xlFormulas).Interior.ColorIndex = 4
    .Replace "=Ds", "Ds", xlPart
    .Replace "dS", "=dS", xlWhole, , True
    .SpecialCells(xlFormulas).Interior.ColorIndex = 4
    .Replace "=dS", "dS", xlPart
  End With
End Sub

Note that I made each "ds" the same color, but given the construction of the code, you can color each one differently if you would like.

 

[Rick Rothstein]

One more thing.... is possible to block the amount of times for 8 or 9 that the 'ds' appears per line???

I cannot think of a good way to do that. First off, it would kill any semblance of randomness in the results. Secondly, I think it would be a nightmare to implement... if you hit the "limit" then you would have to move one of the "ds" values to another row where it might then exceed the limit... trying to manage 32 columns of this kind of shuffling across 6 rows would be quite a daunting programming challenge I would think (and maybe risk hitting an infinite loop along the way). My thought would be if you do not like the distribution, just run the macro again and again until you do like the distribution.

 

[mdorey]

I cannot think of a good way to do that. First off, it would kill any semblance of randomness in the results. Secondly, I think it would be a nightmare to implement... if you hit the "limit" then you would have to move one of the "ds" values to another row where it might then exceed the limit... trying to manage 32 columns of this kind of shuffling across 6 rows would be quite a daunting programming challenge I would think (and maybe risk hitting an infinite loop along the way). My thought would be if you do not like the distribution, just run the macro again and again until you do like the distribution.

Man thank you so much any way you really help me a lot but really a lot :D the code is perfect any way :D it really works :D hehehe i'm really happy :D

 

[neugna]

This thread seems to be the closests thing to what I'm looking for (and I've searched a lot). I have tried to modify the above code and am at a loss (as well as trying cpearson and ozgrid unique random functions).

The application is for a quick re-assigning and posting of audition letters for the 2nd round of an audition. I need a function or sub that will return a list of unique random letters where the amount of letters selected from, and the range they are returned to, is equal to a different amount each time. In other words, I need to randomly assign letters A-G to a group of 7 advancing from one room. But then letters A-L to a group of 12 advancing from another room. All rooms are on the same sheet and I know I could use a "Countifs" to determine the amount from one room versus another.

Any ideas or help will be much appreciated.

Thanks,
Nate

 

[wigi]

Hello,

- can yous show us what code from this topic comes the closest to whay you need

- can you insert the range references in the code so that the ranges are already good? A COUNTA function will do instead of COUNTIF, if I understand your needs correctly.

 

[neugna]

Wigi,

Thanks for the quick reply. Well the code I have is a mess and not getting really close to what I need. I started with cpearson's unique random longs and used a lookup formula to change them to letters: http://www.cpearson.com/Excel/RandomNumbers.aspx

But that only works in an array format and I couldn't sort after pasting the array (which will be essential). Then I used ozgrid's but it pastes the numbers all in one cell: http://www.ozgrid.com/VBA/RandomNumbers.htm

So on this thread, I found yours which works closest to what I need but doesn't allow for letters like "AA, AB, AC, etc"...and I figure out how to change the range for who is advancing from each room.

Then mikerickson's on this thread allowed for separate letters but would not limit the range of letters its choosing from...it returns all that are in the list.

Please forgive my noob-ness and the lenghty story but I only use VBA this one time of year so my memory comes back slow every time. I re-read VBA for dummies and get further each year. So my db has a lot of VBA usage now that makes the whole thing faster. But this one I've been tring to solve for three years now and can't.

Is there a way to post a sample excel sheet? I tried but couldn't.

Thanks,
Nate

 

[wigi]

Hello, read Peter's message, here:
http://www.mrexcel.com/forum/showthread.php?t=508133