Ok,

For those following this thread, I now have code that performs the wa
I want, but I still would rather do the same solution programicall
instead of bouncing between worksheet functions and VBA. Read on.


My question for Rob, or anyone else watching, is whether they see a wa
to do what I have done programatically instead of using the workshee
for part of functionality. As you will note from the explanatio
below, I have achieved what I want by placing some text onto th
workbook and processing it there with functions and then bringing th
text back into the text box....but this slows down the cod
somewhat...(in my end solution the user needs to be able to enter a 50
word paragraph into the text box.)

To reproduce exactly what I have:

(1) Copy this and insert it into range D1:D34:

=""
quick
brown
fox
jumped
over
lazy
dog
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u
v
w
x
y
z

(2) Copy the following into range E1:E34
9898
2575
7565
3435
3676
1564
7564
3765
1111
2222
3333
4444
5555
6666
7777
8888
9999
1122
2233
3344
4455
5566
6677
7788
8899
9911
2323
2424
2525
2626
2727
2828
2929
3030

IMPORTANT: Highlight the range D1:E34 and name the range "dictionary"

(3) In cell C1 insert this formula:

=IF(AND(A1="",B1=""),"*EMPTY*",IF(A1="",B1,A1))

copy it down to cell C50. Name this range "testrange".

(4) Create Userform1. Add one text box "Textbox1", and one comman
button "CommandButton1". In properties of textbox1, set multline t
TRUE. Insert the following code into the Declaration section:

_______________
Private Sub CommandButton1_Click()
Dim str() As String
Dim i As Long, k As Long

str = Split(TextBox1.Text, " ")

k = 1
For i = LBound(str) To UBound(str)
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
Next

UserForm1.Hide
End Sub
_______________
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger
ByVal Shift As Integer)

Dim str() As String, strTemp As String
Dim lngIndex As Long, Bindex As Long, i As Long, j As Long, k As Long
cellStr As Long
Dim n As Long
Dim test As Long
'
If KeyCode = Asc(" ") Then
str = Split(TextBox1.Text, " ")
k = 1
m = 1
' Check first letter to last letter, then look to see if string i
dictionary
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("dictionary"), 2
False)
'
' If NOT in Dictionary then START PARSE CODE
If Err.Number Then
Err.Clear
MsgBox "Word not in dictionary: '" & str(i) & "' ;Pars
Required."
For j = 1 To Len(str(i))
strTemp = Mid(str(i), j, 1)
lngIndex = Application.VLookup(strTemp
Range("dictionary"), 2, False)
If Err.Number Then
Err.Clear
MsgBox "sub-string not in dictionary: " & strTemp
Else
Sheet1.Cells(k, 2).Value = strTemp
k = k + 1
End If
Next
Else
Sheet1.Cells(k, 1).Value = str(i)
k = k + 1
End If
Next

Set rng = Range("testrange")
pStr = ""
For Each cell In rng
' Look to see of the string "*EMPTY*" is in the cell
If cell.Value < "*EMPTY*" Then
pStr = pStr & cell.Value & Chr(32)
End If
Next
TextBox1.Text = pStr

End If
End Sub
_______________

--
Message posted from http://www.ExcelForum.com