VBA to Replace looked up values on Multiple worksheets

[dhiresh]

HI..
Can someone help me with a bit of VBA. I have tried working out codes on the attachment but I have very, very little knowledge of it so am stuck.

I currently have a workbook with several worksheets and on the main worksheet called ‘CUSTOMER_INFORMATION’ (see attached example) I want to replace German words with English equivalent where they match with the list in LANG_Conversion (Column A is formula based values).

For code that I started - See macro called “ChangeTo_English”

Thanking you in advance.
Warm Rgds
Dhiresh

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[dhiresh]

Hi John,
Thx for the code but when transfered to my live file it does not work and the line ws.UsedRange.Replace Range("E" & i).Value, Range("C" & i).Value highlighted in yellow.

One point to make here is that my files contains certain cells that are Merged. Would this cause an issue?

Rgds
Dhiresh

[JOHN H. DAVIS]

Yes. My experience merged cells always cause a problem. I don't know of a work around either.

[dhiresh]

I have de-merged cells and tried but am having the same issue.

Can you suggest what can I do to overcome this problem

Dhiresh

[JOHN H. DAVIS]

Did you change the Sheet name. In Post 1 you call it "CUSTOMER_INFORMATION", however in your sample you call it "ORIGINAL_INFO". The code provided worked for me on the sample.

[dhiresh]

Hi John,
yes. I amended the VBA but could not get it to work on the actual file. I am attaching the file that gives more realistic view and on this file I want to have texts in cells (CUSTOMER_INFORMATION TAB) C12 to C79, F14, F18 to F31 and so on coverted to appropriate English translations from LAND_CONVERSION sheet where A3 to A75 are formula that determines the words. Cell B1 is a dropdown to select which language to apply.

Hope you are able to assist.

many thanks

dhiresh

[JOHN H. DAVIS]

I only changed the sheet names and used Column A vrs Column C on LAND_CONVERSION. I tested it on your current sample and it seems to work.

Please Login or Register  to view this content.

[nilem]

as an option (see attachment)

[dhiresh]

Hi Nilem,

The VBA on the attached file works and many thx for your assistance. This is much appreciated.

However, my actual file contains details in other part of the CUSTOMER_INFORMATION tab and I do not want these to change. Secondly your code changes data up to row 71 and then gets stuck on line (highlights in yellow) - .Replace x(i, 1), y(i, 1), xlWhole. I have tried various ways to identifying what/where the problem is but am unsuccessful as I have very, very little knowledge of VBA.

Would it be possible for you help change the code such that details in rows C12 to C71 and F14 to F71 only, update rather than the whole sheet.

Look forward to hearing from you soon.

regards

Dhiresh

[nilem]

try

Please Login or Register  to view this content.

[dhiresh]

Hi Nilem,

Excellent. Thank you for your help. the codes you supplied works on the test file but when I update the live file (which contains formulae, colours with texts and other validation references. the error is the same i.e. the code gets stuck on line (highlights in yellow) - .Replace x(i, 1), y(i, 1), xlWhole.

Any idea what it could be?

rgds
Dhiresh

[nilem]

Can you show the file in which the error occurs?

[dhiresh]

Hi Nilem
Sorry I could not reply earlier but I have worked out where the problem is. I have adapted your VBA codes and it all works fine.
I like to take this opportunity to thank you very much for your help.

Rgds
Dhiresh