Creating a macro to search string in a range of cells and replace while leaving original

[JCarlos]

Hi all, I am new to this forum. Hope I can get some help on this matter.

So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell. Any ideas on how can I go and do this on Excel? Any links to other forum threads I can read on ?

Thanks in advance.

JCarlos

[JOHN H. DAVIS]

I think the best way to start is to create a list in a separate sheet which has maybe the English version in say Column A and the Spanish version in Column B of the various instructions. Attach a copy of the workbook to this thread with some sample (non sensitive data). Review the Forum Rule Page on how to attach samples. Then we could work from there to create a macro.

[JCarlos]

Hi John ,

Thanks for the prompt answer. I attached an oversimplified example of my workbook , just to get the sense of what I am trying to do. Hope this helps to clarify that.

[JOHN H. DAVIS]

Does this help?

Sub JCarlos()
Dim rcell As Range
Dim i As Long
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Sheets("Special Instructions Tab")
Set ws2 = Sheets("TRanslation Lists")
ws.Activate
For Each rcell In ws.Range("A3:A" & Range("A" & Rows.count).End(3).Row)
    ws2.Activate
    For i = ws2.Range("A" & Rows.count).End(3).Row To 2 Step -1
        If rcell.Value Like "*" & Range("A" & i).Value Then
            rcell.Value = rcell.Value & "\\" & Range("B" & i).Value
        End If
    Next i
ws.Activate
Next rcell
    
End Sub

[JCarlos]

Hi John,

I tried running it and for some reason the first time I tried it it gave me a range error (runtime error 9) so I changed the name of my ws2 to just 'translation' and it runs. However, the only thing printed out in the cell are the "\\" which are attached in the original cell at the end of the original statement. But no equivalent Spanish translation is printed out. Any ideas why?

Thanks in advance

[nilem]

Hi JCarlos,
try also this

Sub ertert()
Dim x, y, i&
With Sheets("TRanslation Lists ")
    x = .Range("A1:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
With Sheets("Special Instructions Tab")
    With .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    If Not .Find("\\") Is Nothing Then MsgBox "Translation already made", 64: Exit Sub
        y = .Value
        For i = 1 To UBound(x)
            .Replace x(i, 1), x(i, 2)
        Next i
        x = .Value
        For i = 2 To UBound(x)
            If Len(x(i, 1)) Then x(i, 1) = y(i, 1) & "\" & x(i, 1)
        Next i
        .Value = x
    End With
End With
End Sub

[JCarlos]

Hi nilem,

I tried your code. When I click on the box, it gives me a copy of the original instructions in the cell but no spanish equivalent. It gives me " \Special Instruction 1\Special Instruction 2\Special Instruction 3\\Special Instruction 1\Special Instruction 2\Special Instruction 3." Is there something missing in the code that relates each special instruction to the Spanish equivalent ? Ultimately the idea is to have the following in a single cell : "Spanish SI1\Spanish SI2\ Spanish SI3\\Special Instruction1\Special Instruction2\Special Instruction3.

Thanks