Nested loop hangs Excel

[ri051zhorzh]

Hi,

I have a problem with a nested loop in my macro as Excel hangs every time i run my script.
I suspect it is because my sheets have too many elements.

I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.
I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.
In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.
Into sheet2 I copied the old english in column A and slovenian translation in column B.
On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC.
So, this should fill most of the translations I need to do, and the rest I will do manually

I am quite new to VBA so this is what I came up with:

Please Login or Register  to view this content.

The number of rows is around 44000 in each sheet.
Can someone advise me as to how i could optimize or even completely avoid the nested loop, because each time i click the button i need to wait for almost an hour and then Excel hangs.

Thank you

[Norie]

If you have 44000 rows on each sheet you'll be looping 44000*44000 = 1,936,000,000 times.

I think you should be looking at using VLOOKUP or INDEX/MATCH.

For example this will look for the value from A1 in column A on Sheet2 and return the corresponding value from column B on sheet2.

Please Login or Register  to view this content.

[Hawkeye16]

Personally I would just use a formula (INDEX/MATCH) - copy it down - and then copy/paste special values to retain just the answers.

Or an untested version of your code

Please Login or Register  to view this content.

[watersev]

hi ri051zhorzh, can you post a sample workbook (around 50-100 rows will be enough) and result expected on Sheet 3?

[watersev]

VB option, press Run button or run code "test" on Sheet1

[ri051zhorzh]

Thank you for your quick replies, I am writing this from another computer and will post a sample work book in a few hours when i am back at my PC

[watersev]

you can test your data on the file provided in post #5

[ri051zhorzh]

Watersev thank you a million!
I tried testing my data in the file you provided and it works perfectly!
It finished all 44000 records in less than 2 seconds! I can not thank you enough!

[watersev]

you are welcome