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?
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.
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.
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.
Private Sub FillFormulas() Dim Cll As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each Cll In Sheets("Master").Range("C5:O32") Cll.Formula = "=IFERROR(HLOOKUP($B" & Cll.Row & ",'" & Format(Application.WorksheetFunction.EoMonth("12/31/2012", Cll.Column + Cll.Row - 7), "mmmm yyyy") & "'!$C$2:$O$35,34,FALSE),0)" Next Cll Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Oh, and want to learn another trick?
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.=Date(Year(A1),Month(A1),0)
Last edited by Whizbang; 05-05-2015 at 04:37 PM.
This is how I would do it.
Last edited by Whizbang; 05-06-2015 at 03:32 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks