Hi,

I am trying to write a subroutine to translate English sentences to
Japanese words.
I have a list of sentences "dictionary" worksheet.

dictionary worksheet

English Japanese
A fjjdkslfds
B fsdfsdf

The problem is, excel worksheet functions like sort/countif/vlookup starts
to produce #value results when a single cell contains more than 255
characters.

If I do a vlookup on a cell(A) 255 characters , vlookup produces #value
even if I copied and pasted cell(A) into the dictionary worksheet.

Is there any workaround for this "limitation"? Is this a limitation?

I understand Excel is not supposed to be used to handle large amounts of
text. For most parts of the translation, it is only 1 or 2 sentences (so
they don't exceed 255 characters) - and they worked fine. There are only a
few that do exceed 255.

My subroutine works for cases < 255. I have tried using find and mid
functions to do partial comparisons with mixed results -- still a few got
through the net. arrgh!

My Code

Sheet1 is the english worksheet
sheet1Translated is translated worksheet (target)
dictionary is used to look up sentences. it contains every cell in sheet1
(column1) and japanese translation (column2).

For Each CurCell In Worksheets("sheet1").Range(Cells(1, 1),Cells(LastRow,
LastCol))
temp = Len(CurCell.Value) / 255
If temp 1 Then
With Worksheets("Dictionary").Range("A:B")
Set n = .Find(Mid(CurCell.Value, 1, 255),
LookIn:=xlValues,Lookat:=xlPart)
If Not n Is Nothing Then
Worksheets("sheet1Translated").Range(CurCell.Addre ss).Value
= n.Offset(0,1).Value
End If
End With
Else
Worksheets("sheet1Translated").Range(CurCell.Addre ss).Value = _

Application.VLookup(CurCell.Value,Worksheets("Dict ionary").Range("A:B"), 2,
False)
End If
Next

Also, is there anyway to use other coding techniques to make it run faster?
it is not too slow with 20,000 cells to translate but since I am new to vba
so I am sure there is a better/faster/cleaner way to do this.

Thanks in advance.