Translate words using other sheet.

[jasond1992]

Good day. Looking for a help, I want to translate all the words on sheet1 base on the sheet2 that I create.

on "Sheet 1" there's sample words. that I want to be translated. I have a button on "sheet 1" when you click the button it will translate all the words on "Sheet 1" Base on the words I input on "Sheet 2" "Column B"

on "Sheet 2" "Column A" the reference words.
on "Sheet 2" "Column B" words that I wanted to be translat on "sheet 1"

Thanks for the help in advance. GOD Bless.

[Marc L]

As any beginner can use Excel basics like the Search / Replace feature …

Anyway according to your attachment another method as a VBA starter :

Sub Translator() 
    With Sheet2.UsedRange.Columns 
        W = .Item(1).Value2 
        X = .Item(2).Value2 
    End With 
    With Sheet1.UsedRange.Columns(2) 
             V = .Value2 
        For R& = 2 To UBound(V) 
                 S = Split(V(R, 1)) 
            For C& = 0 To UBound(S) 
                 Z = Application.Match(S(C), W, 0) 
                If IsNumeric(Z) Then S(C) = X(Z, 1) 
            Next 
                V(R, 1) = Join(S) 
        Next 
            .Value2 = V 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jindon]

jasond1992,

It is only "Replacing", not "Translating" means the results are terrible...

[Marc L]

Translating can be easily done under VBA piloting IE but slow … I don't know if exists some API.

[Marc L]

According to the attachment the VBA translation API version :

Private Sub Translator() 
   Const URL = "https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=ja&dt=t&q=" 
     Dim L&, V, R&, W, X, oReq As Object 
         L = Sheet2.UsedRange.Rows.Count 
    With Sheet1.UsedRange.Columns(2) 
            V = .Value2 
        For R = 2 To UBound(V) 
                W = V(R, 1) 
          If Asc(W) <> 63 Then 
                X = Application.Match(W, Sheet2.UsedRange.Columns(1), 0) 
            If IsNumeric(X) Then 
                V(R, 1) = Sheet2.UsedRange.Cells(X, 2).Text 
            Else 
                If oReq Is Nothing Then 
                  .Font.ColorIndex = 15 
                   Set oReq = CreateObject("WinHttp.WinHttpRequest.5.1") 
                End If 
                   Application.StatusBar = "       Web  translating  cell  B" & R 
                   oReq.Open "GET", URL & V(R, 1), False 
                   oReq.setRequestHeader "DNT", "1" 
                   On Error Resume Next 
                   oReq.send 
                If oReq.Status = 200 Then 
                   V(R, 1) = Split(oReq.responseText, """")(1) 
                   If V(R, 1) > "" And V(R, 1) <> W Then L = L + 1: Sheet2.[A:B].Rows(L).Value2 = Array(W, V(R, 1)) 
                End If 
                   On Error GoTo 0 
            End If 
          End If 
        Next 
           .Value2 = V 
        If Not oReq Is Nothing Then 
           .Font.ColorIndex = xlAutomatic 
            Application.StatusBar = False 
            Sheet2.UsedRange.Columns.AutoFit 
            Set oReq = Nothing 
        End If 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄