damienm38


Hello,

I am implementing a lookup Method to find translation matches in XL tables.

For example, if I call the Method: Translate("world") in the following cells:

A | B

--------------------------------------------------------------------------

hello world | bonjour le monde

world | monde

I'd expect the result "monde" to be returned by my Translate("world") function.

Apparently - and the VB debugger confirms it - the 1st occurence of the cell containing world is found - e.g "bonjour le monde", but then calls to FindNext returns "Nothing" while I would have expected it to keep looking for the next occurence of "mother", and of course find the match.

Since the Search function of XL works with my example, I'd appreciate if someone could have a cross check on the following Method.

Thks,

Damien

Function Translate(P_textToTranslate As String)
Dim L_translation_sheet As Worksheet
Dim L_text As String
Dim L_range As Range
Dim L_firstCell As Range
Dim L_cell As Range

' Need to update/extend the range in case we go beyond table end
Set L_range = Worksheets("Translation").Range("a3:a500")

With L_range
Set L_cell = .Find(P_textToTranslate, _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

Do While Not L_cell Is Nothing
If L_firstCell Is Nothing Then
Set L_firstCell = L_cell

ElseIf L_cell = L_firstCell.Address Then
' we are back at beginning of search range
Exit Do
End If

If (L_cell.Text = P_textToTranslate) Then
' We have found the translation
' Hardcode: shift 1 cell to the right to retrieve the French translation
L_text = L_cell.Next.Text
Translate = L_text
Exit Function
Else
Set L_cell = .FindNext(L_cell)
End If

Loop 'of Do

If L_cell Is Nothing Then
Translate = "TRANSLATION NOT FOUND"
Else
' Some match was found but it was not the exact same
Translate = "<-- Translate"
End If
End With
End Function




Re: FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells

Andy Pope


Hi,

Given your code and example data, which I put in cells A4:B5, your routine finds both occurances of world but returns monde as it is an exact match for world.








Re: FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells

damienm38

Hi,

thanks for your response. So it confirms that the logic of the code is correct.

I am using a system with VB 63. and XL 2003 SP2.

Knowing that the Search function of XL does work and find both occurences in this example, I would suspect a configuration issue. Any idea which obscure parameter could control the behavior - I have already tried all optional parameters from the Find Method

Damien






Re: FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells

Andy Pope

I can not think of a flag that would cause the symptoms you describe.

You could check the options that are set when you use CTRL+F to find the text. Make sure no format searching is set.

If you can share a workbook of sample data I will see if I can reproduce your problem.

My website has email contact details
www.andypope.info





Re: FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells

damienm38

Hello,

I have tried running the Translate() method on another PC, still using XL 2003. Same issue...
I have also noticed that the sample code provided in Microsoft help does not work either on this same system...

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Can you clarify which versions of XL + VB you are using for my sample code to work

thks,

Damien




Re: FindNext returns "Nothing" when doing iterative lookups, though data to find is in the cells

Andy Pope

xl2003 sp2
VBA 6.5