Solved: Place a string in a defined direction

[alstubna]

Hello!

This is kind of complicated so hang in there!

I'm "attempting" to build a word search puzzle template.

I guess everyone knows what a word search puzzle is, don't they?

I'm trying to automate (sort of) entering the word list into the matrix of cells.

Lets say I have a word (string) in cell A100. In cell B100 I'll type in a cell address of where I want the word in cell A100 to begin. In cell C100 I have a drop down list with 6 selections, 1 to 6. These selections are the directions (orientation) in which I want the word from cell A100 to follow.

For example. if the word in cell A100 is "TRY" and the address in cell B100 is G20 and the selection in cell C100 is 1 then I want the word in cell A100 to be entered as individual letters T R Y in cells G20, H20 and I20.

I want to put this inside a worksheet_change event based on the drop down list in cell C100.

That much I can do. What I can't do is figure out how to get the word parsed out by letter into the individual cells. The example I gave above is one of the easier orientations, horizontal all on the same row. If someone can show me how to do the hardest orientation then I should be able to figure out the others. I guess the hardest one would be top to bottom, diagonal, right to left. It would look something like this:


Word = try
address: G20
direction: 6

_____E_____F_____G_____H
20_______________T_____
21_________R___________
22__Y__________________

Seems like I need some kind of loop that pulls the first letter of the word, places it into G2, pulls the second letter, offsets G20 based on the desired direction and the number of the character based on the length of the word, places the letter, etc, etc.

This doesn't seem to be "real easy" to me!!!!

If someone can show me how to do this one example I should be able to figure out the rest (I hope!).

Thanks for any assistance you can offer!
Al

[mikerickson]

Some variation of this might work for you. This uses a Select Case to test which of the 8 directions is indicated and puts the string in that direction, starting at writeCell.

Sub test()
Dim myString As String
Dim writeCell As Range
Dim i As Long, a As Long, b As Long
Dim dropDownString As String

dropDownString = "Up Right"
myString = "abcde"
Set writeCell = Range("e30")

Select Case dropDownString

    Case Is = "To Right"
        a = 0: b = 1
    Case Is = "Up Right"
        a = -1: b = 1
    Case Is = "Straight Up"
        a = -1: b = 0
    Case Is = "Up Left"
        a = -1: b = -1
    Case Is = "To Left"
        a = 0: b = -1
    Case Is = "Down Left"
        a = 1: b = -1
    Case Is = "Straight Down"
        a = 1: b = 0
    Case Is = "Down Right"
        a = 1: b = 1
End Select

For i = 0 To Len(myString) - 1
    writeCell.Offset(a * i, b * i) = Mid(myString, i + 1, 1)
Next i

End Sub

[alstubna]

Thank you, Mike!

I was able to easily adapt it to my needs.

That loop isn't as complicated as I thought it would be.

Thanks
Al