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

 

[wigi]

Hello mdorey

Welcome to MrExcel

Did you check on this page:
http://www.ozgrid.com/VBA/RandomNumbers.htm

Admittedly, it allows you to have unique random numbers (not letters) but you could tweak the function to cater for letters instead of numbers.

 

[wigi]

Anyway, I wrote the code myself:

Sub RandomLetters()
    
    Const sCharacterSet As String = "NMTEDS"
    
    Dim cell As Range, sOutput As String
    For Each cell In Range("C10:AG10")
        sOutput = ""
        Do While Len(sOutput) < 2 * Len(sCharacterSet)
            sLetter = Mid(sCharacterSet, WorksheetFunction.RandBetween(1, Len(sCharacterSet)), 1)
            If InStr(sOutput, sLetter) = 0 Then sOutput = sOutput & " " & sLetter
        Loop
        cell.Resize(Len(sCharacterSet)) = Application.Transpose(Split(Trim(sOutput)))
    Next
End Sub

 

[wigi]

A little easier to digest:

Sub RandomLetters()
    
    Const sCharacterSet As String = "NMTEDS"
    
    Dim cell As Range, sChars As String, sOutput As String, i As Long
    For Each cell In Range("C10:AG10")
        sOutput = "": sChars = sCharacterSet
        For i = 1 To Len(sCharacterSet)
            sLetter = Mid(sChars, WorksheetFunction.RandBetween(1, Len(sChars)), 1)
            sChars = Replace(sChars, sLetter, "")
            sOutput = sOutput & " " & sLetter
        Next
        cell.Resize(Len(sCharacterSet)) = Application.Transpose(Split(Trim(sOutput)))
    Next
End Sub

 

[mikerickson]

I think this will do what you want.

Sub test()
    Dim Letters As Variant, LetterCount As Long
    Dim ColNum As Long, i As Long
    Dim randIndex As Long, temp As String
    Dim DestinationRange As Range
    
    Set DestinationRange = Range("C10:ag15")
    Letters = Array("N", "M", "T", "E", "DS")
    
    LetterCount = UBound(Letters) + 1
    Set DestinationRange = DestinationRange.Resize(LetterCount)
    
    For ColNum = 1 To DestinationRange.Columns.Count
        For i = 0 To LetterCount - 1
            randIndex = Int(Rnd() * LetterCount)
            temp = Letters(randIndex)
            Letters(randIndex) = Letters(i)
            Letters(i) = temp
        Next i
        DestinationRange.Columns(ColNum).Value = Application.Transpose(Letters)
    Next ColNum
End Sub

 

[mdorey]

I think this will do what you want.

Sub test()
    Dim Letters As Variant, LetterCount As Long
    Dim ColNum As Long, i As Long
    Dim randIndex As Long, temp As String
    Dim DestinationRange As Range
    
    Set DestinationRange = Range("C10:ag15")
    Letters = Array("N", "M", "T", "E", "DS")
    
    LetterCount = UBound(Letters) + 1
    Set DestinationRange = DestinationRange.Resize(LetterCount)
    
    For ColNum = 1 To DestinationRange.Columns.Count
        For i = 0 To LetterCount - 1
            randIndex = Int(Rnd() * LetterCount)
            temp = Letters(randIndex)
            Letters(randIndex) = Letters(i)
            Letters(i) = temp
        Next i
        DestinationRange.Columns(ColNum).Value = Application.Transpose(Letters)
    Next ColNum
End Sub

Thanks very much friend... i will try it today then i give you a feedback :D thanks for to time on this

 

[Rick Rothstein]

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

 

[wigi]

Rick had an interesting suggestion to loop through the columns of the range. If I change my code with this loop, it would look like:

Sub RandomLetters()
    
    Const sCharacterSet As String = "NMTEDS"
    
    Dim col As Range, sChars As String, sOutput As String, i As Long
    For Each Col In Range("C10:AG15").Columns
        sOutput = "": sChars = sCharacterSet
        For i = 1 To Len(sCharacterSet)
            sLetter = Mid(sChars, WorksheetFunction.RandBetween(1, Len(sChars)), 1)
            sChars = Replace(sChars, sLetter, "")
            sOutput = sOutput & " " & sLetter
        Next
        col = Application.Transpose(Split(Trim(sOutput)))
    Next
End Sub

 

[Rick Rothstein]

sLetter = Mid(sChars, WorksheetFunction.RandBetween(1, Len(sChars)), 1)[/code]

Just to point out, the WorksheetFunction does not support RandBetween in XL2003 because it is part of the "Analysis ToolPak" add-in. I presume there is a way to get at this function via the "Analysis ToolPak - VBA" add-in, but I have not played around with it, so I am not completely sure of how.

 

[shg]

Tools > References, tick atpvbaen.xls, and then use as it were a native VBA function.

Hideously, every invocation of an ATP function in VBA prints to the Immediate window.