Nesting greater than 7 levels with IF function

jmschiff

New Member
Joined
Oct 10, 2002
Messages
1
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top