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.

 

[Norie]

And the formula does what exactly?

 

[mikerickson]

What do you mean by converting to VBA?

To add an array formula to a range, the syntax would be like

Range("G1:G10").FormulaArray = "=SUM(IF(A1:A10=3,5))"

To make the same calculation as an array formula, but using VBA instead, it is often easier to approach the problem ex novo. The Evaluate funcion can also be used.

 

[rossn13]

And the formula does what exactly?

Apologies, it's a bit ugly. Here's the other worksheet, it might make things a bit clearer.
It selects the opponents for the next match using the date and team as references. Could be a home or away game. Displays in caps if the game is at home and lower case if its away.

 

[rossn13]

What do you mean by converting to VBA?

To add an array formula to a range, the syntax would be like

Range("G1:G10").FormulaArray = "=SUM(IF(A1:A10=3,5))"

To make the same calculation as an array formula, but using VBA instead, it is often easier to approach the problem ex novo. The Evaluate funcion can also be used.

I'm just after a much more efficient method than the array formula that I'm currently using. I've not delved much into VBA before (Other than simple macro editing) and wondered if it could be used in this case somehow?

 

[Norie]

Sorry but this really isn't clear.

What are the 2 sheets called?

In the posted formula you refer to 'Fixtures'?

Which sheet is that?

Also where are you using the formula?

 

[rossn13]

Sorry but this really isn't clear.

What are the 2 sheets called?

In the posted formula you refer to 'Fixtures'?

Which sheet is that?

Also where are you using the formula?

Top worksheet is Fixtures, the formula is in the second worksheet (Team 1) cell F7. And copied to all the other cells in the "fixture grid" S7:AJ17

 

[rossn13]

Apologies for the bump but I'm trying to get this sorted before the season starts

 

[Norie]

I'm afraid I still don't get what's going on and it's kind of hard to check things based on images.

For example, in F7 of the first worksheet the result is QPRBol.

In the second worksheet F7 has Che (Chelsea?), and it's against the goalkeeper P (Petr) Cech.

Where's the connection?

Am I missing something completely obvious?

Can you upload the workbook and post a link?

PS You could try adjusting the current formulas to use more specific ranges instead of working withe entire columns.

 

[rossn13]

Here's the workbook:
http://www.filefactory.com/file/cde73e7/n/TFF_11-12_WIP.xlsm

Sorry if I'm being unclear!
F7 (players Club) in the second worksheet (Team 1) has to match either column D (home matches) or E (away matches) in the first (Fixtures), for each of the dates in row 3 (Team 1, second worksheet). In other words I'm extracting Chelsea's fixtures, both home and away, from a list of all the Premier League fixtures.

Thanks for helping btw Norie