# Nesting greater than 7 levels with IF function

#### jmschiff

##### New Member
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 to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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!

Hope this helps

Chris

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.

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

You could try:

=CHAR(RANDBETWEEN(65,90))

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

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

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

Replies
18
Views
1K
Replies
2
Views
337
Replies
0
Views
208
Replies
4
Views
1K
Replies
0
Views
384

1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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

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