Speeding up the macro (defining a table)

[mumsys]

Hi, in the attached workbook, i have a simple macro that puts formulas into the sheet and filling down the values etc.
Point is, that it take very long (and one of my workbook contains even 20 000 of the raw data). So my question is - is there a solution to my problem? It should make the same output but take much less time (because now its really useless). Please run the macro and see what i mean.

The output what i need is the table from G2:Z to the downEnd (only the values) that i can paste in another workbook. I guess there should be a table defined inside the macro or something but i don't really know how to start. : If the code is not running I pasted it below

Please Login or Register  to view this content.

[humdingaling]

try taking the column formula out
its not needed and the calculations for that is large

also replacing vlookup with index/match may improve performance
here is link that taught me how index/match works and advantages over vlookup for large data sets like your example

http://www.randomwok.com/excel/how-to-use-index-match/

[:) Sixthsense :)]

Turn off/on screen updating and check.

[Leith Ross]

Hello mumsys,

This macro will greatly speed up the process. Only the values are saved into the columns, no fomulas are created or evaluated, and the look ups can be be done at random.

Please Login or Register  to view this content.

[mumsys]

Thanks Humdingaling, thats very nice

Leith Ross, hi and thank You for the work. But i have some problems maybe because im using office 2007 and something do not work as at Your place.
Firstly i had an error in line

Please Login or Register  to view this content.

but i changed Sheet2 to sheets("Mapping") and it worked. The same thing - I changed all of the Sheet1 and sheet2 to my workbooks names and ok. But on line

Please Login or Register  to view this content.

i get application-defined or object defined error. (here i also changed Sheet1 to sheets("sheet1")

Best regards

[mumsys]

OK it worked i see my mistake in a few minutes ill get a feedback how it manage to work with lot of data

[mumsys]

This is awesome! Easily adjustable and quick. Thank You Leith Ross!

[Leith Ross]

Hello mumsys,

Glad you got it working. I am using Excel 2010 but none of the macro is unique to 2010. So, I am mystified by your problem.
The Sheet1 and Sheet2 in the macro are the Excel Code Names for the sheets. These names are read only and can not be changed. This is how Excel and VBA keep track of them since the user can change the worksheet name.

You are probably asking "Isn't a Sheet and a Worksheet the same thing?" Excel can have 3 different types of Sheets: Worksheets, Charts, and Dialog. Each of these has its own name property that the user can change to identify them.
For Worksheets, the Worksheet name property defaults to the Sheet Code naem i.e., Sheet1, Sheet2, Sheet3. In the VB editor you will see the Sheet code name followed by the Worksheet name in parentheses, like this, Sheet1(Sheet1).

When writing macros for other people, using the Sheet Code name is often a better choice than using the Worksheet Name, since the user may change the Worksheet name later on. By using the Sheet Code name, the reference will always point to the same Worksheet. This scheme has been used since Windows '95. I have attached a copy of the workbook with I macro I used for testing. It is the one you posted. See if runs on your system.