Filling Cell Values with Random Alphabet

[dk54]

Hey guys. I'm working on a vba function that randomly inputs a letter of the alphabet A-Z into a specified destination range.

I'm thinking something like this:

Sub Alpha_Gen()
Dim row as integer
Dim col as integer

for row = 1 to 20
for col = 1 to 10
cells (row, col) = [no idea what to put here to randomly generate something A-Z]

next row
next col

End sub

What do you guys think? Is there a statement that does something like this?
Or do I need to create an array that holds the alphabet and call from it randomly somehow?

 

[shg]

One way:

Sub Alpha_Gen()
    With Range("A1:J20")
        .Formula = "=char(65 + randbetween(0,25))"
        .Value = .Value
    End With
End Sub

For Excel 2003-, requires the Analysis Toolpak

 

[njimack]

maybe this:

Function Rand_Alpha()

Rand_Alpha = Chr(WorksheetFunction.RandBetween(65, 90))

End Function

 

[Andrew Poulsom]

Without the Analysis Toolpak:

Sub Alpha_Gen()
    Dim row As Integer
    Dim col As Integer
    For row = 1 To 20
        For col = 1 To 10
            Cells(row, col) = Chr(65 + (Rnd * 26))
        Next col
    Next row
End Sub

 

[VoG]

Try

Sub Alpha_Gen()
Dim row As Integer
Dim col As Integer
Randomize
For row = 1 To 20
    For col = 1 To 10
        Cells(row, col).Value = Chr(65 + Round(Rnd() * 26))
    Next col
Next row
End Sub

 

[shg]

Peter, that will generate half as many As and [s as the other characters.

 

[VoG]

OK, sorry. At least they'll be a different non-random set each time the code is run

 

[shg]

Andrew is keeping you company

 

[dk54]

Mr. Poulson,

Thank you that seemed to have worked. And thanks for indenting the code for easier reading. I am eager to learn good habits early on.

I was wondering if you could provide some assistance in helping me understand how the char( ) and rnd ( ) arguments work in this example.

I am reading into it and understand that char (65) represents A with 65 through 90 Representing A-Z.

I am having a bit of trouble understanding the rnd part of the code.
The VBA Help explains that rnd(number) function returns a random "single" data type.

So in the Chr(65+ (Rnd *26)) part of the code is
"65+" : returns a single data type greater than 65 (the lower bound)

(Rnd*26) - randomly 26 times?
Is the "+" used here to be taken as concatenation?

So if I were to write Chr(91- (rnd*26) would it provide the same result?




Shg -
A question I have is if there is an advantage of using the analysis tool pack? Is it faster or easier on the memory demand? What situations would it be clearly useful? I am running Excel 2007. I know I am to have read of Excel history if I am to use it but I have only been familiar with vba in the past week. Why can your code only run on 2003 with the toolpack?

 

[shg]

The functions in the Analysis ToolPak were changed to mainstream worksheet functions in Excel 2007, so it doesn't apply to you.

My code puts a formula in the cells, and the formula includes a function from the ATP (pre-2007). Then the formulas are replaced with their results.