Word Scramble

[jasgot]

I want to make a word scramble for my Nephew.

Lacking a Scramble() function I looked at Rand() and made no progress.

I would like to take the word in column A and have it scrambled in column B. Like so.

dragonfly gfoadryln

I would also like to do it with two words;

Ice Cream rmciaeec

And one last requirement; keep font formatting. One letter will be red in word or phrase, the red letters will spell a clue about the scrambled words. ie If you read down the column, the red letters might spell 'fun at the zoo'

Any ideas? I can't seem to find a way to scramble or shuffle the letters.....

 

[PaddyD]

with just the "scrambling", you could use formulas. with the extra font requirement, this is definitely vba...in which case, care to be a bit more specific about how your data's set up etc...

paddy

 

[Yogi Anand]

Hi jasgot:

As Paddy said, the first part is doable with the formulas. See the following worksheet simulation for dragonfly ...

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	dragonfly	d	r	a	g	o	n	f	l	y
3	randomnumbers	7	1	4	5	3	8	2	9	6
4	fdgoalryn	f	d	g	o	a	l	r	y	n
5
Sheet6

</SPAN>

I have utilized the MRAND function from the MoreFunc add-in to create a dynamic variation. One can also choose to create a number of static arrays of the random numbers to create various alternates consisting of the characters in the base word.

As for preserving the font color of one of the characters, you will have to employ VBA.

 

[jasgot]

Wow! Who would of thought Excel counldn't do this on it's own.

Where do I get MoreFunc, and, If I use your equation in b2,c2,d2 ...., which all appear to be the same, they all generate a the first character, shouldn't column b have the first char and column c have the second char and so on?

 

[jasgot]

MRAND() gives me a #NUM! error

This is my cell:
=MRAND(14,1)

the result is #NUM!

There must be something wrong...

 

[Jaafar Tribak]

Hi Jasgot,

I have created a Custom Function called (Scramble).

Paste this code in a standard module in your Workbook or Personal.xls:<pre>
Public Function scramble(s As Variant)

On Error Resume Next
Dim CL As New Collection
Application.Volatile
scramble = ""

Do Until CL.Count = Len(s)
R = Int(1 + Rnd * Len(s))
CL.Add R, CStr(R)
Loop

For I = 1 To CL.Count
scramble = scramble & Mid(s, CL(I), 1)
Next

End Function</pre>

If you want to randomlly change the Font color of a single charactere within a Scrambled string then you could place a Comandbutton (Comandbutton1)on the worksheet and assign it the following code:<pre>
Public Sub CommandButton1_Click()


For Each CEL In Me.Range("B3:B13").Cells
CEL.Font.Color = vbBlack
CEL.Value = scramble(CStr(CEL))
CEL.Characters(Int(1 + Rnd * Len(CEL)), 1).Font.Color = vbRed
Next


End Sub</pre>

Now everytime you hit the CommanButton,the Font Color of a single charactere in a specified String.This charactere changes at Random each time.


To illustrate this,See the following Sample Worksheet :

sheetcontrol.xls
	A	B	C	D	E
1	Original	Scrambled And	********	Original	Srambled
2		Font Color Changed		String	with Formula
3	JAAFAR	RFJAAA		JAAFAR	AJFARA
4	COBOS	OOCSB		Jasgot	sagtJo
5	JUAN GONZALES	SNNAGOEJZUA L		Yogi Anand	aYidngnAo
6	ARMANDO	NRMADAO		PaddyD	DPdday
7	MOHAMED	EADMOHM
8	JOHN GIBBARD	HAB NROIGBJD
9	MARK	AMKR
10	CLAIRE	CAIRLE
11	Josh	ohsJ
12	MARK ROW	RMAOWRK
13
14
Sheet3

Note that within each name in the Range B3:B12 there is actually a Random Charactere whose Font Color is Red.This changes every time the CommandButton is Clicked.
Unfortunately,when converted to HTML,these colored characteres don't show.


Hope this helps.

Jaafar.
This message was edited by rafaaj2000 on 2003-02-05 19:51

 

[Yogi Anand]

Wow! Who would of thought Excel counldn't do this on it's own.

Where do I get MoreFunc, and, If I use your equation in b2,c2,d2 ...., which all appear to be the same, they all generate a the first character, shouldn't column b have the first char and column c have the second char and so on?
----------------------------
MRAND() gives me a #NUM! error

This is my cell:
=MRAND(14,1)

the result is #NUM!

There must be something wrong...

Hi jasgot:

The MoreFunc add-in can be downloaded from ...

http://longre.free/fr/english/

The formula in my post is an array formula -- you enter the formula in cell B3, highlite cells B3:J3, then do CTRL+SHIFT+ENTER (i.e. while holding the CTRL and SHIFT keys, press the ENTER key).

You may also want to readup about use of array formulas in Excel Help.

 

[Yogi Anand]

Hi Jaafar:

The scramble UDF is Beautiful!

 

[Jaafar Tribak]

Thanks Yogi for the compliment

Jaafar.

 

[Ekim]

Well done, Jaafar.

In Excel 97-SR2, the ME command in the following line results in a compile error:

For Each CEL In Me.("B3:B13").Cells

However, if you change ME to ActiveSheet, the code works perfectly.

Regards,

Mike