Simple translating VBA program find and replace

[psboyboy13]

Hello everyone,

I have the following attached excel sheet. I have just begun using VB in excel and was wondering how to go about this. In sheet 1, I want the user to use the vocabulary on the top, and type any of those words (all in lowercase) into the textbox next to "English". When the user clicks the commandbutton labeled "Translate" I want it to find the values in sheet 2, Column B for French, and I want it to print in the textbox in sheet 1 next to French, the words in french. Could anyone shed some guidance as to how to start this? Thanks!practice.xls

[Artik_PL]

In Sheet1 module:

Private Sub CommandButton1_Click()
  Dim arrEng As Variant
  Dim arrFrGer As Variant
  Dim pos As Integer
  
  With Worksheets("Sheet2")
    arrEng = .Range("A1").CurrentRegion.Columns(1)
    arrFrGer = .Range("A1").CurrentRegion.Columns(1).Offset(, 1).Resize(, 2)
  End With
  
  On Error Resume Next
  pos = Application.Match(Me.TextBox1.Value, arrEng, 0)
  On Error GoTo 0
  
  If pos = 0 Then
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Exit Sub
  End If
  
  Me.TextBox2.Value = arrFrGer(pos, 1)
  Me.TextBox3.Value = arrFrGer(pos, 2)

End Sub

Artik

[psboyboy13]

Thanks for your input Artik,

I didn't mention earlier, but it's supposed to allow the user to type multiple words to form simple sentences. However your code is only allowing one word at a time.

Currently I'm doing:
A = Replace(TextBox1, "the", "la")
A = Replace(A, "is", "est")
A = Replace(A, "on", "sur")
A = Replace(A, "my", "mon")
A = Replace(A, "yellow", "jaune")
A = Replace(A, "red", "rouge")
A = Replace(A, "friend", "ami")
A = Replace(A, "sick", "malade")
A = Replace(A, "hat", "chapeau")
A = Replace(A, "pencil", "crayon")
A = Replace(A, "table", "table")

B = Replace(TextBox1, "the", "dem")
B = Replace(B, "is", "ist")
B = Replace(B, "on", "auf")
B = Replace(B, "my", "mein")
B = Replace(B, "yellow", "gelb")
B = Replace(B, "red", "rot")
B = Replace(B, "friend", "freund")
B = Replace(B, "sick", "krank")
B = Replace(B, "hat", "hut")
B = Replace(B, "pencil", "bleistift")
B = Replace(B, "table", "tisch")

TextBox2.Value = A
TextBox3.Value = B

This doesn't use the 2nd sheet at all, and also am pretty darn sure there's a much more easier way to put it. Thanks guys!

[Artik_PL]

s'il vous plaît

Private Sub CommandButton1_Click()
  Dim arrEng As Variant
  Dim arrFrGer As Variant
  Dim pos As Integer
  Dim arrSentenceEng As Variant
  
  Dim strWord As String
  Dim strSentenceFr As String
  Dim strSentenceGer As String
  Dim i As Integer
  
  With Worksheets("Sheet2")
    arrEng = .Range("A1").CurrentRegion.Columns(1)
    arrFrGer = .Range("A1").CurrentRegion.Columns(1).Offset(, 1).Resize(, 2)
  End With
  
  arrSentenceEng = Split(Me.TextBox1.Value, " ", -1, vbTextCompare)
  
  For i = 0 To UBound(arrSentenceEng)
    strWord = arrSentenceEng(i)
    pos = 0
    
    On Error Resume Next
    pos = Application.Match(strWord, arrEng, 0)
    On Error GoTo 0
    
    If pos = 0 Then
      strSentenceFr = strSentenceFr & " " & "(no word)" & " "
      strSentenceGer = strSentenceGer & " " & "(no word)" & " "
    Else
      strSentenceFr = strSentenceFr & " " & arrFrGer(pos, 1)
      strSentenceGer = strSentenceGer & " " & arrFrGer(pos, 2)
    End If
  Next i
  
    Me.TextBox2.Value = Trim(strSentenceFr)
    Me.TextBox3.Value = Trim(strSentenceGer)

End Sub

Artik