Another case of Find & Replace from a List

Hi All,

I am frequently are generating Power Point presentations based on charts imported from Excel. Everything is initially developed in Spanish, but time & time again, we must translate contents to English and on ocassions to Portuguese.

What I have been doing up to know in Excel is to include all my fixed texts with an =IF()... formula that changes the text depending on a "language setting" coded somewhere on that workbook. For example:

Supposing I am on Sheet1, my language code is set in cell A2 (1=Spanish; 2=English);

The formula I use in all my fixed text is something like: =if(Sheet1!$A$2=1;"Mi Texto";"My Text")

Furthermore, all my charts are created using the normal references to ranges; and in the case of chart Titles, I am specifying these in reference to a "cell" that holds the "textual value" I need to show on the chart.

So far this works, albeit the time it takes to change all fixed texts to include the "=IF().." formula noted above, not to mention the diminishng response time when apllying the above to data in tables to be further exploited through Pivot Tables.

Given the same text is used more or less repetitively throughout all the worksheets we build , I was wondering if a better (faster, more efficient, standardized etc.) way to get this translation done could be to use a macro and a dictionary (in the form of a table) holding the terms we regularly use as well as their corresponding translation to the languages we require.

I have read http://www.excelforum.com/excel-prog...from-list.html and this is could be something like the solution / macro I am looking for.

I am attatching a small example of a spreadsheet, the idea would be to "replace" all text strings found in 'sheet1' using the list that appears on sheet 'dictionary'.

Any help that could be provided and example code I could follow (I am very novice to macros and vbas) would be much appreciated.

Kind regards, DMurray3

Hi

Perhaps you could do it like this.

Instead of writing words on Sheet1 you link appropriate cell to your "dictionary" sheet and use a macro to copy the English or Spanish word "blocks" to a target range.

Have a look at the file "TranslationExample2" and see if this fits your need.

Alf