VBA Dictionary Macro to find and replace (translate) text

[excelstarter1]

Hey guys, I am Robert and new to this forum. As I am an Excel/VBA beginner, I hope you can help me with my problem. My head is already in pain

I am trying to build a VBA dictionary macro. What is the idea/problem: I have an Excel file which my company is using all the time to consolidate different information from e.g. subsidiary companies or customers. Our main Excel file is in English (and the content is always changing). However, we would like to have a macro to automatically translate all the content from the English Workbook to another language e.g. Spanish or Italian.

Example Table in this original English Excel File:

Customer	C1	...	...
Country	Denmark	...	...
Name	...	...	...
Item	...	...	...
Price	100	...	...

<tbody>
</tbody>

Basically the macro should load an Excel File which works as a dictionary (with column 1 containing English word, column 2 Spanish translation, column 3 Italian translation etc.). Then I want the macro to find and replace all the words/text in the original English Workbook (highlighted in blue in above table) with the Spanish or Italian Translation (from the dictionary file).

Basically it is a loop for each cell in the whole workbook (all worksheets) by comparing each cell content (text) with the dictionary and replacing the original content with the translation (from the dictionary).

Does anyone have an idea how to start??

I read a lot about the "Scripting.Dictionary" object, but I dont think this will work in my case as I have more than 2 columns in the dictionary (column 1: original English, colum 2: Spanish translation, column 3: Italian translation and so on).

Thank you very much in advance!!!

Best regards
Robert

 

[RickXL]

Any thoughts on the other points? I am still struggling

Yes, but I have learned to answer only one at once. Otherwise you get answers like: Yes, it worked, and it takes the rest of the week to find out exactly which answer or answers that applied to.

To make it happen for all worksheets you need to loop round the worksheets:

    For Each ws In ThisWorkbook.Worksheets
        With ws
            For i = 1 To lr - 1
                .Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
            Next
        End With
    Next

As I do not now the names of your workbooks or worksheets I can't improve very much on that.


Regards,

 

[excelstarter1]

Sorry, just returned to the office an hour ago. Again, the code works perfectly. Thank you so much!

Does the current code run through ALL cells of the worksheet and thus slows down the macro or just the cells with content? I was wondering becaus of the LookAt:=xlWhole


I couldnt find any hints yet with regard to the other issues. Do you have an idea?

(3) How can I add formulas with text to the find and replace routine?
e.g. a formula is ="customer "&A1
Now I want the macro to translate the word "customer" within the formula. Do you have an idea?

(4) How can I add titles/text in charts to the translation routine?

 

[RickXL]

LookAt:=xlWhole means look at whole cell contents not at every cell in the spreadsheet. There is documentation available on the internet about the various options: Range.Replace Method (Excel)

I think it is looking only at cells with something in - but I could be wrong. How long is it taking? Is that too long?

I just tried changing ="YYY"&A1 from YYY to ZZZ and it seemed to work. What happened when you tried?

Charts could be a whole separate issue. How do you change them now? I would probably start by using the Macro Recorder to log the changes you currently make then see if that can be automated.


Regards,

 

[excelstarter1]

Right now it does not take too much time. I was just wondering in case an excel file gets very large.

I also tried changing ="YYY"&A1 from YYY to ZZZ as you suggested but it did not work... It just kept showing YYY.When I try it with the regular search and replace command in Excel (search for YYY), it says it could not find a match in the formula ="YYY"&A1. I dont understand the issue...

 

[RickXL]

The Range Replace method in the macro is currently set to look at whole cells only. (LookAt:=xlWhole) That will need to be changed to look at partial cells.
Change it to: LookAt:=xlPart. The problem with that will be that if the words to be translated are only a part of other words then they will be translated - possibly out of context.

I cannot tell whether that will be an issue for you or not as I do not know what your data is.


Regards,

 

[excelstarter1]

I tried around with the code - and the LookAt:=xlWhole vs LookAt:=xlPart - and will stick with the xlWhole. Otherwise, as you mentioned, it will translate some words out of context. Much safer this way.

RickXL, thank you so much for all your help!

 

[excelstarter1]

Hi RickXL,

I got a follow-up question after trying out the new macro for the last couple of days. It works perfectly by the way. So thanks again!

I was wondering how I would exclude cells with formulas in them when running the macro. My current approach just slows down the macro and crashes most of the time. Do you have an idea how to implement it so that the macro runs smoothly and almost as fast as before?

Thanks in advance!
Regards

For Each ws In ThisWorkbook.Worksheets
With ws
For Each r In ThisWorkbook.Worksheets.Cells
If r.HasFormula = False Then        
For i = 1 To lr - 1
.Cells.Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Else
End If
Next r
End With
Next

 

[RickXL]

Try something like this:

Sub Test()
    Const lang1 As String = "C"
    Const lang2 As String = "D"
    Dim arr1    As Variant
    Dim arr2    As Variant
    Dim i       As Long
    Dim lr      As Long
    Dim ws      As Worksheet
    
    With ThisWorkbook.Worksheets("Words")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr1 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
        arr2 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
    End With
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Select Case (ws.Name)
                Case "CopyOfData", "MoreData"
                    For i = 1 To lr - 1
                        .Cells.SpecialCells(xlCellTypeConstants).Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                    Next
            End Select
        End With
    Next
End Sub

Notes:
1) I added a Select Case statement so that I can list the worksheets I need to be processed.
2) SpecialCells(xlCellTypeConstants) will look at the cells containing constant values only. Here is a list of the possible values you can use: XlCellType Enumeration (Excel)

You might prefer to use a worksheet selection method that excludes sheets rather than explicitly includes them. In which case this should work:

Sub Test()
    Const lang1 As String = "C"
    Const lang2 As String = "D"
    Dim arr1    As Variant
    Dim arr2    As Variant
    Dim i       As Long
    Dim lr      As Long
    Dim ws      As Worksheet
    
    With ThisWorkbook.Worksheets("Words")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr1 = .Range(.Cells(2, lang1), .Cells(lr, lang1))
        arr2 = .Range(.Cells(2, lang2), .Cells(lr, lang2))
    End With
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Select Case (ws.Name)
                Case "DoNotProcess", "Sheet1"
                Case Else
                    For i = 1 To lr - 1
                        .Cells.SpecialCells(xlCellTypeConstants).Replace What:=arr1(i, 1), Replacement:=arr2(i, 1), LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                    Next
            End Select
        End With
    Next
End Sub

It will skip worksheets called "DoNotProcess" and "Sheet1".


Regards,

 

[excelstarter1]

So sorry for the late reply.

Thank you very much RickXL! I just tried out the amended code and it works pretty smoothly so far

Will try it out the next couple of days. Hope you dont mind if I get back to you in case I run into another small issue with the code

Regards

 

[LPMM_PT]

Sorry for restoring such an hold topic, but I am currently using this method and would like to know the following:

Is it possible to change a cell content in two parts? Meaning for example:

Cell = "Current number of tries is 7. Caution"

Is it possible to put in the dictionary the following keys and translate in two parts:
Key1 = "Currente number of tries is"
Key2 = "Caution"

Thank you and regards