Convert Array formula to VBA to speed up worksheet

Hi there!

Last night a Mod very kindly posted the following formula for me:

=INDEX($1:1,1,MATCH(CD2,2:2,0))

I filled the cells as follows:

=INDEX($1:1,1,MATCH(CD3,3:3,0))
=INDEX($1:2,1,MATCH(CD4,4:4,0))
=INDEX($1:3,1,MATCH(CD5,5:5,0))

And so on, all the way down to

=INDEX($1:365,1,MATCH(CD367,367:367,0))

Although the 366(!) formulas (1 for every day of the year) return the correct result, they slow down the worksheet considerably.

Is there a way that that the same thing could be achieved using VBA, with the objective of running my worksheet as efficiently as it did before?

Many thanks!

What do these formulas actually do?

Perhaps a link to the original thread might be appropriate.

If you are having efficiency problems due to these problems are you sure that using code will resolve them?

Hi Norie, thanks for responding.

Here's the link:

www.excelforum.com/showthread.php?t=622401

The sheet ran very smoothly before I used the formulas but it's certainly slower now.

Thanks again.

I've taken a look at that other thread and I don't see any reference to array formulas.

Normally (always?) array formulas need to be confirmed with CTRL+SHIFT+ENTER but I don't see anybody suggesting you do that.

I'm sorry, I wrongly assumed it was because I'd understood array formulas slow down performance of worksheets

I still think all these formulas are slowing it down and was wondering if it was somehow coded if that would speed up the sheet?

I wouldn't have thought that those formulas would slow calculations significantly, although I'd be inclined to restrict the formula to the specific range required, i.e. try this formula in CE3 copied down

=INDEX(A$1:CC$1,MATCH(CD3,A3:CC3,0))

Daddylonglegs - YOU DID IT! It works absolutely brilliantly now, thank you so much for your reply, this means a lot to me - you really have made my day!