Nesting greater than 7 levels with IF function

[jmschiff]

I would like to generate an entire page of randomly chosen letters (one letter in each cell) I have tried to use a combination of the IF and RAND functions however I am limited to 7 nestings, as opposed to 26 for each letter. Can someone suggest a better method to do this which falls under the nesting limit.

 

[ChrisUK]

To get round this limit use select case:

select case MYLETTER
case "a"
do something
case "b"
do something
case else
do someting
end select

you can go on here for ages!

I posted something about this earlier this week with some example code as well

Hope this helps

Chris

 

[Dave Patton]

Put letters down a column say A1:A26.
Put =RAND() in the next empty column. Say B1
Copy or FillDn
Convert the random numbers to constants:
copy (ctrl+c), edit, paste special, values
Sort on the random numbers column
Delete the random numbers column.

 

[Legacy 98055]

Hi
=CHAR(RANDBETWEEN(65,90))
will generate random, uppercase letters.
=LOWER(CHAR(RANDBETWEEN(65,90)))
for lower case.

Tom

This will randomly select Upper or Lower case.
=IF(RANDBETWEEN(1,2) = 1,LOWER(CHAR(RANDBETWEEN(65,90))),CHAR(RANDBETWEEN(65,90)))
This message was edited by TsTom on 2002-10-11 12:37

 

[Aladin Akyurek]

You could try:

=CHAR(RANDBETWEEN(65,90))

 

[ChrisUK]

Ummm read the post again and seems I've not understood the question correctly.

How about some VBA

set rng = Range("A1:f10")
for each c in rng
c.value = asc(rnd(26)+64)
next

I've not tried it but it should work.

HTH

Chris

 

[chef]

there has been many postings re limit of 7 nestings..you can get around this..altough I am usually humbled by Aladins solutions.....but...you can get around 7 nestings by naming your nest as a formula...eg if test1=...then test2..with test1 as 6 ifs..if you know what I mean..

regards chef