Convert formula into VBA code

[janjan_376]

My B9:B40000 in Sheet1 contain the below formula. I have noticed slowness in excel when it starts the calculation. Is there a way to convert them into vba code?
Please help. Thank you.

Please Login or Register  to view this content.

[sweep]

Hi,

This really shouldn't cause a noticable slow down of your sheet.

Do you have many volatile functions or array functions in your sheet as well?


Also, you could make the formula slightly less complex like this:

Please Login or Register  to view this content.

[garretonufer]

post your workbook...

[martindwilson]

you could probably drop the a9="" as well as that would give n/a if blank anyway
=IF(ISERROR(MATCH(A9,Sheet2!$A$2:Sheet2!$A$40000,0)),"",INDEX(Sheet2!$B$2:Sheet2!$B$40000,MATCH(A9,Sheet2!$A$2:Sheet2!$A$40000,0)))

[broro183]

hi,

Yes, they can be converted to VBA, but I've got a couple of suggestions first...

To halve the number of calculations, change your formulae to:
cell B9

Please Login or Register  to view this content.

cell C9

Please Login or Register  to view this content.

Or (if I have understood correctly?) you could use
cell B9 =

Please Login or Register  to view this content.

Do your formulae really need to go down to row 40,000?
- The fewer rows you need to use the easier on Excel. You could use a "dynamic named range" (run a search for this) to cover the data range shrinking or expanding.

hth
Rob

[janjan_376]

Thanks for your time and suggestions. I did try them but it still slowing the excel performance. Although I am on cell 13,000 yet.

Yes, the formula would even go down to 60,000.

Note: there are also cells that contain small formulas that I think I would not be necessary to mention here as my main concern is this big formula.


Please help. Thank you.

[broro183]

hi,

What version of Excel are you using?
How many other files do you have open?
Are you filtering the sheet too?
Is it only slow to calculate initially, or repeatedly?
(this is likely due to the limits of Excel's calculation dependency tree which will only allow so many formulae before it rebuilds the calculation tree at every calculation.)
You may find some useful info on Dave &/or Charles' sites:
www.mvps.org/dmcritchie/excel/excel.htm
http://decisionmodels.com/calcsecrets.htm
http://decisionmodels.com/optspeedb.htm

I strongly recommending only copying the formula down for as many rows as is currently necessary & if the data will remain static, I recommend copying & paste special'ing as values once the calculation is complete.

Are you aware that any macro approach is likely to be a "one off" ie the same as copying & paste special'ing?

I'm sorry I'm off to bed now & don't have time to write a test macro at the moment. You could record a macro as you complete the steps manually & this will give us a base code to modify for you...
If you still want a macro approach after reading Charles' site, can you please post a sample workbook as Garretonufer requested (& include your recorded code)?

hth
Rob