Slow code- making code more efficient

[ammartino44]

Hello. I added this code to about 200 cells in a tab. =IFERROR(HLOOKUP($B16,INDIRECT("'"&TEXT(EOMONTH("12/31/2012",COLUMN(F16)+ROW(F16)-7),"mmmm yyyy")&"'!"&"$C$2:$O$35"),34,FALSE),0)

Now, my workbook is running very slow. When I ran a macro that worked perfectly before, it froze up. I think that has to do with these formulas. The formulas look for different tabs in the workbook and then do an hlookup. Is there a more effecient way of writing this formula?

[Whizbang]

Do you mind posting your VBA code and sample workbook as well?

The trick is to get rid of the INDIRECT, as it is volatile. I realize one of your other threads was about whether INDIRECT was needed, but that thread did not have the context of what your end-goal is.

The way I see it, you seem to be writing this formula in a cell, then copying/filling down. If the cells do not shift around after that (meaning no cut/paste or insert/delete) then these values are more or less static, and so can be generated without INDIRECT some way. I believe the best approach, without having seen your workbook or knowing your end-goal, is to loop through the cells in question and write a less volatile formula into them, taking advantage of VBA's power and flexibility.

[ammartino44]

Ok, I just copied one tab (the other info is confidential). It has the lookups (they don't return anything because the tabs have been taken out). Why is the indirect volatile? Please let me know how to improve. Thanks.

[Whizbang]

It is volatile becuase you are asking Excel to interpret a string as a reference. This means, to Excel, that the string could change at any time, so must recalc every time there is a change in the workbook. This slows things down when you have lots of INDIRECTs.

Can you provide the basic structure of the other worksheets? I assume they are all the same, just different data. If so, it might be beneficial to have them all on one table and then have a monthly summary sheet or something that displays the month's data using formulas and a selection tool of some sort. This will limit the number of sheets and eliminate the need for INDIRECT.

Also, Can I see your VBA code? Here is how I would write it, not knowing exactly what you need it for.

Please Login or Register  to view this content.

Oh, and want to learn another trick?

Please Login or Register  to view this content.

This will give you the end of month for the month prior to A1. EOMONTH works just fine, but this is another trick. You can do things like "=Date(Year(A1),Month(A1)+6,0)" to shift months around. I find it easier to read than EOMONTH, but that is just a preference. It also works on all versions of Excel.

[Whizbang]

This is how I would do it.