How to convert a CSE to VBA?

[rossn13]

Hi there gurus!
I have the following array formula repeated a lot of times, and it's making my spreadsheets completely unusable

It would brilliant if somebody could explain how I'd go about converting it to VBA? Or even better if somebody could do it for me!?

I'd be eternally grateful

{=IF(ISERROR(INDEX(Fixtures!$E:$E,MATCH(S$3&$F7,Fixtures!$C:$C&Fixtures!$D:$D,0))),IF(ISERROR(INDEX(Fixtures!$D:$D,MATCH(S$3&$F7,Fixtures!$C:$C&Fixtures!$E:$E,0))),"",LOWER(INDEX(Fixtures!$D:$D,MATCH(S$3&$F7,Fixtures!$C:$C&Fixtures!$E:$E,0)))),UPPER(INDEX(Fixtures!$E:$E,MATCH(S$3&$F7,Fixtures!$C:$C&Fixtures!$D:$D,0))))}

Image is of fixtures worksheet.

 

[rossn13]

Bump ray:

 

[Norie]

I've had a look at the file, and I'm not 100% sure the formulas are the only things makind it unusable.

There's a lot more going on than that, even some sort of (web) query.

It actually took about 2-3 minutes to open on my machine and that was using both processors at 100%.

What in words does the formula actually do?

Is it simply meant to got the fixtures sheet and find out the player's team is playing on a particular date?

I'm pretty sure there's better ways to do this.

The only thing is, what happens when the team isn't playing on a particular date?

One thing I would suggest doing is not concatenating for the MATCH.

I can have another look later but I doubt you'll get anything for tomorrow.

 

[rossn13]

Cheers Norie,
It's definitely the array formulas that slow it down. They're all deleted bar 1 in the copy I sent you, and it runs fine. The web queries run when you open the file. They pull Fixture lists, injury lists and player points data from the web. Should run smoothly once they've executed.

I want a fixture grid, somewhat similar to the one on this website
http://bramernic.com/fsports/footgrid.php?div=P&extra=N
Except it displays the opposition team name instead of a red square. The blank spaces are as important as the actual fixtures. It shows me how many times teams play in between other teams fixtures.

 

[Norie]

So you've sorted it by changing the array formulas?

 

[rossn13]

So you've sorted it by changing the array formulas?

I'm still using the array formulas, or rather have them 'deactivated' because they're too slow. Can you think of another method to do this? It's basically an INDEX, MATCH where 2 columns need to match. These are the date column and either the home or away column.

 

[Marcelo Branco]

Maybe the cause for a so bad performance is the use of references to entire columns in an array-formula like:

Fixtures!$E:$E
Fixtures!$C:$C&Fixtures!$D:$D
...
...

Just for testing-purposes try to use a specific range or a dynamic-named range

M.

 

[Norie]

Marcelo has a very good point.

If you reference an entire column you are referencing over 1 million cells.

So any formulas which involve entire rows is going to end up with a lot whole of calculating.

You've also got formulas like this:

=IF(A4=SUBSTITUTE(A4,"OFF",""),IF(A4="","",IF(RIGHT(A4,2)="11","",IF(RIGHT(A4,2)="12","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A4,LEN(A4)-6,7,"")," v ",""),"Arsenal","Ars"),"Aston Villa","Ast"),"Blackburn","Blb"),"Bolton","Bol"),"Chelsea","Che"),"Everton","Eve"),"Fulham","Ful"),"Liverpool","Liv"),"Man City","MC-"),"Man Utd","MU-"),"Newcastle","New"),"Norwich","Nor"),"QPR","QPR"),"Stoke","Sto"),"Sunderland","Sun"),"Swansea","Swa"),"Tottenham","Tot"),"West Brom","WB-"),"Wigan","Wig"),"Wolverhampton","Wol")))),"")

Now I think I know what that's supposed to do and if I'm right this could be done with VLOOKUP.

 

[rossn13]

ray:ray:


Thank you so much Norie and Marcelo! Just spent aaaaaaaaaaages changing all the $A:$A references and it's now like grease lightening!

Thanks again guys.

 

[Marcelo Branco]

Hey rossn13

Great!!!

M.

 

[Norie]

Glad you got it working.

There's probably a few other things you could do but probably best to leave it for a bit.