simple text replacer

[andreymd87]

hi. i am trying to create a simple translator of text in cells.

i have one worksheet named "smeta" wich contains russian words. i need to create a macro that refers to another worksheet named (translator) and search those words there and replace them in same place in "smeta". the translator sheet is a table that contains russian words and next to them english translated words. so the question is. how to make this macro? i am very new to these things. thanks

 

[EFANYoutube]

Hi adreymd87
The below code will do what you want
Put it in a module and in the cell where you want the answer put

=TRANSLATE(B6)

Change B6 to the word your trying to find
It will give a value error if the word does not exist

Function TRANSLATE(SearchWord As String) As String
    TRANSLATE = ThisWorkbook.Sheets("TRANSLATOR").Range("A:A").Find(SearchWord).Offset(, 1).Value
End Function

 

[andreymd87]

thanks. this works for a cell. but i wondered if the macro can search and replace all words in the sheet automatically. without typing the function

 

[EFANYoutube]

Yeah it can, could you provide a snippit of what your sheet looks like

 

[andreymd87]

i need all these russian words to be translated automatically in the same sheet, same cells when i run the macros.

 

[EFANYoutube]

Ok, I have changed the code to work for your needs
Please change where it says Sheets("Name of sheet") to be the name of the sheet youve shown above
You can also add this code (RunTranslate) to a button on the devaloper tab

Sub RunTranlate()
    For Each cell In ThisWorkbook.Sheets("Name of sheet").UsedRange.Cells
        If TRANSLATE(cell.Value) <> "Error" Then
            cell.Value = TRANSLATE(cell.Value)
        End If
    Next cell
End Sub


Function TRANSLATE(SearchWord As String) As String
    Dim FoundRng As Range
    Set FoundRng = ThisWorkbook.Sheets("TRANSLATOR").Range("A:A").Find(SearchWord)
    If FoundRng Is Nothing Then
        TRANSLATE = "Error"
    Else
        TRANSLATE = FoundRng.Offset(, 1).Value
    End If

End Function

 

[andreymd87]

something happening but still wrong results. notice that the cells with #name? have formulas wich give also words. in the translator sheet is only one word added for testing. Артикул. wich is translated as articol. but as you can see here is the same name in cells instead of translated one.

 

[EFANYoutube]

Hi,
Please check that the spelling is correct in each sheet and that there are no spaces after any of the words