Word Scramble

jasgot

New Member
Joined
Jul 16, 2002
Messages
22
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi jasgot:

As Paddy said, the first part is doable with the formulas. See the following worksheet simulation for dragonfly ...</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

New Member
Joined
Jul 16, 2002
Messages
22
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

New Member
Joined
Jul 16, 2002
Messages
22
MRAND() gives me a #NUM! error

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

the result is #NUM!

There must be something wrong...
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
Office Version
2016
Platform
Windows
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
ABCDE
1OriginalScrambled And********OriginalSrambled
2Font Color ChangedStringwith Formula
3JAAFARRFJAAAJAAFAR
4COBOSOOCSBJasgot
5JUAN GONZALESSNNAGOEJZUA LYogi Anand
6ARMANDONRMADAOPaddyD
7MOHAMEDEADMOHM
8JOHN GIBBARDHAB NROIGBJD
9MARKAMKR
10CLAIRECAIRLE
11JoshohsJ
12MARK ROWRMAOWRK
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,427
Office Version
2016
Platform
Windows
Thanks Yogi for the compliment :)

Jaafar.
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
 

Forum statistics

Threads
1,078,488
Messages
5,340,638
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top