Results 1 to 10 of 10

Thread: Word Scramble
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,234
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jasgot:

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

    ******** LANGUAGE="JavaScript" ************************************************************************>
    Microsoft Excel - Book1___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    2
    dragonflydragonfly
    3
    random numbers714538296
    4
    fdgoalrynfdgoalryn
    5
    Sheet6

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    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.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    New Member
    Join Date
    Jul 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    New Member
    Join Date
    Jul 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    MRAND() gives me a #NUM! error

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

    the result is #NUM!

    There must be something wrong...

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default

    Hi Jasgot,

    I have created a Custom Function called (Scramble).

    Paste this code in a standard module in your Workbook or Personal.xls:


    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



    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:


    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



    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 :


    ******** ******************** ************************************************************************>
    Microsoft Excel - sheetcontrol.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    OriginalScrambled And ********OriginalSrambled
    2
    Font Color Changed Stringwith Formula
    3
    JAAFARRFJAAA JAAFARAJFARA
    4
    COBOSOOCSB JasgotsagtJo
    5
    JUAN GONZALESSNNAGOEJZUA L Yogi AnandaYidngnAo
    6
    ARMANDONRMADAO PaddyDDPdday
    7
    MOHAMEDEADMOHM
    8
    JOHN GIBBARDHAB NROIGBJD
    9
    MARKAMKR
    10
    CLAIRECAIRLE
    11
    JoshohsJ
    12
    MARK ROWRMAOWRK
    13
    14
    Sheet3

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jaafar:

    The scramble UDF is Beautiful!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,395
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    3 Thread(s)

    Default

    Thanks Yogi for the compliment

    Jaafar.

  10. #10
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •