Using FormulaArray Formula code

[pells]

I am trying to put a FormulaArray in many rows in my workbook. My code is as follows:

Range("E11:E81").FormulaArray = "=IF(D11<>"""",IFERROR(INDEX(WSMASTER!$B$2:$B$3980,MATCH(1,(B11=WSMASTER!$C$2:$C$3980)*(D11=WSMASTER!$A$2:$A$3980),0)),""ERROR - this is not a valid!""),"""")"
The problem with this code is that it copies from E11 to E81 and the cell references dont change from 11. For example the formula in cell E12 should be:

{=IF(D12<>"",IFERROR(INDEX(WSMASTER!$B$2:$B$3980,MATCH(1,(B12=WSMASTER!$C$2:$C$3980)*(B12=WSMASTER!$A$2:$A$3980),0)),"ERROR - this is not a valid!"),"")}

Is there a way of changing the code so that from cell E11 to E81, the references highlighted in red above change with it?



Please can anyone help me?

Many thanks for taking the time to read post.

 

[number kruncher]

Pells,

Have you tried Relative cell references, eg RC[1] instead of Absolute references D12?

 

[pells]

Pells,

Have you tried Relative cell references, eg RC[1] instead of Absolute references D12?

This is when you use $'s? Yes, I have tried this and still no luck!

Any other ideas?

 

[number kruncher]

You will need to split your 'formula', so rather than,

IF(D12<>

use IF("&RC[0]&"<>

Use the Ampersand to join non text items together.

 

[pells]

You will need to split your 'formula', so rather than,

IF(D12<>

use IF("&RC[0]&"<>

Use the Ampersand to join non text items together.

Sound good, but you have stumped me!

What does RC[0] mean? My formula array is:

Range("E11:E81").FormulaArray = "=IF(D11<>"""",IFERROR(INDEX(WSMASTER!$B$2:$B$3980,MATCH(1,(B11=WSMASTER!$C$2:$C$3980)*(D11=WSMASTER!$A$2:$A$3980),0)),""ERROR - this is not a valid!""),"""")"

How do I amend this to include your suggestion? Apologies, but learning this stuff.....

 

[number kruncher]

No apologies needed, we are, after all still learning everyday!! (I should use this as a tag line )

Try the following, I have inserted the Relative Cell References, so if the formula gets placed in Cell E11, then Cell D11 is used, for the first and third one, and -3 relates to Col B.

Range("E11:E81").FormulaArray = "=IF("&RC[-1]&"<>"""",IFERROR(INDEX(WSMASTER!$B$2:$B$3980,MATCH(1,("&RC[-3]&"=WSMASTER!$C$2:$C$3980)*("&RC[-1]&"=WSMASTER!$A$2:$A$3980),0)),""ERROR - this is not a valid!""),"""")"

 

[pells]

No apologies needed, we are, after all still learning everyday!! (I should use this as a tag line )

Try the following, I have inserted the Relative Cell References, so if the formula gets placed in Cell E11, then Cell D11 is used, for the first and third one, and -3 relates to Col B.

Range("E11:E81").FormulaArray = "=IF("&RC[-1]&"<>"""",IFERROR(INDEX(WSMASTER!$B$2:$B$3980,MATCH(1,("&RC[-3]&"=WSMASTER!$C$2:$C$3980)*("&RC[-1]&"=WSMASTER!$A$2:$A$3980),0)),""ERROR - this is not a valid!""),"""")"

Many thanks for this.

I have tried it and get the following error:

Compile error: Expected: end of statment

 

[Colin Legg]

When you use the FormulaArray property, if you try to put the formula in multiple cells in one go (as, for example, you might with the formula property), it will create a shared array formula for all of the cells.

There are four ways to do what you want demonstrated in section 3.2 here. The best one depends on whether you're worried about formats, using the clipboard and whether or not the formulas are calculated before stepping to the next line of code.

 

[Andrew Poulsom]

Your formula doesn't return an array, so put it in the first cell and copy it to the remaining cells.

 

[pells]

Many thanks all for your help and suggestions.

I have managed to get this to work successfully by setting A Multi-Cell Array Formula (option 4) here:

http://www.colinlegg.co.uk/Excel/FormulaArray.html

Once again, many thanks for all your help.