Work-around voor verticaal zoeken bij lange teksten

[ArjanVos]

We hebben heel veel Excel bestanden met productgegevens, waarin Chinese vertalingen moeten worden gemaakt van Engelse teksten. Heel veel teksten komen in honderden van die bestanden voor en daar wordt nu telkens handmatig de Chinese vertaling bij gezet. Eén keer vertalen moet voldoende zijn, is mijn idee.

Het idee was dan ook om een centraal bestand aan te maken met Engelse teksten in de ene kolom en de Chinese vertalingen in de kolom ernaast. In de decentrale bestanden worden de Engelse teksten opgehaald uit een database en het idee was om daar dan via verticaal zoeken de corresponderende Chinese tekst bij te zoeken vanuit de centrale database.

Echter, we komen erachter dat de zoekwaarde bij verticaal zoeken niet langer mag zijn dan 255 tekens en dat is geregeld wel het geval.

Heeft iemand een goed idee hoe we ook voor die lange teksten toch kunnen werken met een centrale lijst van vertalingen, zodat we van een hoop handwerk af zijn?

 

[Cobbe]

Kan je niet werken met nummers want via vert.zoeken en lange teksten gaat het geheid fout.
Als er één woord verkeerd gespeld wordt vindt de formule dit niet meer.
Via een nummer daarentegen....

 

[ArjanVos]

Daar zat ik ook heel even aan te denken, maar zo'n nummer komt niet mee vanuit de database en moet je dan dus ook in ieder decentraal bestand handmatig invullen.

Die lange teksten kopieer en plak je in de centrale lijst, zodat je geen spelfouten krijgt, was het idee.

 

[edmoor]

Plaats eens een relevant voorbeeld van dat centrale bestand.

 

[ArjanVos]

Sure! Bij deze

 

[snb]

Kan met een simpele dictionary:

Sub M_snb()
    sn = Sheet1.Cells(1).CurrentRegion
    
    With CreateObject("scripting.dictionary")
        For j = 1 To UBound(sn)
            .Item(sn(j, 1)) = sn(j, 2)
        Next
    
        MsgBox .Item(sn(29, 1))
    End With
    
End Sub

 

[ArjanVos]

Hm, misschien begrijp ik het niet, of ben ik zelf niet helemaal duidelijk geweest. Wat ik dus wil, is dat ik in een andere sheet bijv. in cellen C2:C10 wat teksten heb staan, die ook voor komen in kolom A van mijn voorbeeldbestand. Ik wil dan in cellen D210 de corresponderende teksten krijgen vanuit rij B van mijn voorbeeldbestand (die straks dus verder is gevuld met de Chinese teksten), net zoals je dat via verticaal zoeken zou kunnen doen.

Met jouw voorbeeld krijg ik een pop-up...

 

[snb]

Met de door mij getoonde methode kan dat via een UDF.

 

[ArjanVos]

Misschien komt het door een lange dag werken, maar ik zie nog niet helemaal hoe ik dit moet ombouwen naar een UDF die doet wat ik wil

 

[edmoor]

Bij deze met UDF:
Bekijk bijlage Kopie van translations.xlsb

Code zit in een module en in ThisWorkbook. Voorbeeld van gebruik staat in kolom C.
(Wel eerst even het document opslaan en eventueel deblokkeren)

 

[snb]

Of met een doodnormale array

 

[ArjanVos]

Top! We zijn er bijna...

Nu staat de lijst met vertalingen niet in hetzelfde bestand, maar in "U:\\Product files\translations.xlsx" en moet ik die verwijzing er nog correct inbouwen. Ik dacht zelf iets als dit, maar dat is'm niet:

Sub M_start()

    Dim libfile As Worksheet
    libfile = Workbook("U:\Product files\translations.xlsx").Sheet1

With libfile
    Set lib = Range(.Cells(1, 1), .Cells(2, 50000))
End With

End Sub

Function Translate(eng)

     For j = 1 To UBound(lib)
         If lib(j, 1) = eng Then Exit For
     Next
     Translate = lib(j, 2)

End Function

 

[edmoor]

Om even mijn voorbeeld erbij te halen, dit in het document met de te vertalen teksten:

Dit in een module:

Public EngChin As Dictionary
Public DicLoad As Boolean

Sub MaakDictionary()
    Dim i As Long
    Dim rMsg As Variant
    Dim TWB As Workbook
    
    Application.ScreenUpdating = False
    Set TWB = Workbooks.Open("[COLOR="#FF0000"]U:\Product files\translations.xlsx[/COLOR]", ReadOnly:=True)
    
    Set EngChin = New Dictionary
    With TWB.Sheets("Blad1")
        For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            rMsg = Split(.Cells(i, 1) & vbTab & .Cells(i, 2), vbTab, -1, vbBinaryCompare)
            EngChin.Item(rMsg(0)) = rMsg
        Next i
    End With
    
    TWB.Close
    Application.ScreenUpdating = True
    DicLoad = True
End Sub

Function Vertaal(Engels As Range) As String
    If Not DicLoad Then Call MaakDictionary
    Vertaal = EngChin(Engels.Value)(1)
End Function

Uiteraard hebben gebruikers minimaal leesrechten nodig op de gebruikte locatie en het centrale document.

 

[ArjanVos]

Het heeft even stil gelegen van mijn kant, maar ik krijg het niet voor elkaar. Als resultaat krijg ik telkens #NAAM?

Bijgaand mijn bestand; voor het bestand waarnaar ik verwijs heb ik volledige rechten.

 

[snb]

Of zo:

 

[edmoor]

Het heeft even stil gelegen van mijn kant, maar ik krijg het niet voor elkaar. Als resultaat krijg ik telkens #NAAM?

De module heeft dezelfde naam als de routine. Geef de module een andere naam.
Zorg er tevens voor dat in de Verwijzingen de verwijzing Microsoft Scripting Runtime is aangevinkt.

 

[ArjanVos]

Module andere naam: Check!
Verwijziging Microsoft Scripting Runtime aanvinken: Check!

Resultaat blijft helaas nog hetzelfde

 

[ArjanVos]

Of zo:

Deze geeft een fout op de regel

sn = .Sheets(1).CurrentRegion

 

[edmoor]

Module andere naam: Check!
Verwijziging Microsoft Scripting Runtime aanvinken: Check!

Resultaat blijft helaas nog hetzelfde

In het document dat je hier plaatste werkt dat bij mij prima.
Ga na die wijziging in een cel met die functie staan en druk op F2 en dan Enter.

 

[ArjanVos]

Dat had ik al gedaan Maar helaas dus zonder resultaat. Nog maar een keer het bestand erbij dan...