This code assumes that your Dictionary has a named range of "Dictionary"
Also the Split function is not supported until (I think) Excel 2000, so
you'll need Split97.
Tom has made a 97 version: Do a Google Groups search for: Split97 Tom Ogilvy
Private Sub CommandButton1_Click()
Dim str() As String, strTemp As String
Dim lngIndex As Long, i As Long, j As Long, k As Long
str = Split(TextBox1.Text, " ")
k = 1
On Error Resume Next
For i = LBound(str) To UBound(str)
lngIndex = Application.VLookup(str(i), Range("Dictionary"), 2,
False)
If Err.Number Then
Err.Clear
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 "letter not in dictionary: " & strTemp
Else
Sheet1.Cells(k, 1).Value = strTemp
Sheet1.Cells(k, 2).Value = lngIndex
k = k + 1
End If
Next
Else
Sheet1.Cells(k, 1).Value = str(i)
Sheet1.Cells(k, 2).Value = lngIndex
k = k + 1
End If
Next
End Sub
--
Rob van Gelder -
http://www.vangelder.co.nz/excel
"jasonsweeney " wrote in
message ...
Ok, here is the challenege.
Background: My spreadhseet allows the user to type sentences into a
text box. The individual words in the text box are then parsed and
placed in a worksheet in vertical column "A." In vertical column "B" I
use vlookup functions that match the word to a number. In another part
of the worksheet, I have essentially a dictionary, where I have about
6000 individual words listed in a vertical column. Each word is
assigned a discrete number. (00000, 00001, 00002, etc.). So, after
typing a sentence in the text box (e.g. "I love green eggs and ham"),
the spreadsheet would look like thus:
I 32450
Love 43234
Green 34434
eggs 12304
and 00456
Ham 34543
Problem: Sometimes people type words into the text box that are not in
my "dictonary." When that happens, column B will result in "*NID*",
meaning Not-In-Dictionary. (simple IF function)
What I want to do is to have a macro that, when a word is not in the
dictionary, parses the WORD into letters. So if I type in
"serendipity" into the textbox (which is not in the dictionary), the
macro will translate it to " s e r e n d i p i t y", all letters, which
will be placed in the vertical column (all letters have respespective
numners). Thus, the sentence "I want serendipity" would look:
I 43534
want 45676
s 00345
e 01355
r 13544
e 01355
n 76545
d 33434
i 36567
p 35654
i 36567
t 00345
y 33545
Please see previous posts related to the text box parsing:
http://www.excelforum.com/showthread...hreadid=188803
Any help would be appreciated
---
Message posted from http://www.ExcelForum.com/