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