Entering long array formula using vba

[Pistoleiro]

Hi All,

I have this rediculously long array formula to check whether certain data matchs, Below is the formula however I'm trying to get vba to enter this into cell O3 I'd rather not enter the formula as a normal formula then use send keys but at this point I'm running out of options.

Also if we can avoid using R1C1 reference style that would be helpful as I believe I'd have to do a rewrite of the rest of the workbook to get that to work.

Any ideas?

Thanks very much

=IF(ISERROR(IF(VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)=(SUM(IF($D$2:$D$14=D3,IF(H3=$H$2:$H$14,IF(I3=$I$2:$I$14,$J$2:$J$14,0),0),0))-VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)),"OK","CHECK")),"",IF(VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)=(SUM(IF($D$2:$D$14=D3,IF(H3=$H$2:$H$14,IF(I3=$I$2:$I$14,$J$2:$J$14,0),0),0))-VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)),"OK","CHECK"))

 

[Sandeep Warrier]

Hi,

You mostly won't be able to use this formula in any macro because it crosses the 255 character limit.

If you tell us what the formula does perhaps someone may be able to come up with a better way of getting results.

 

[Colin Legg]

Hi,

Entering an array formula in VBA is done via the Range.FormulaArray property. There is a limit of 255 characters imposed, but your formula is well over 400 characters. There are workarounds to this character limit but the bottom line is that the formula needs to be rebuilt from scratch to make it into something more manageable. In addition to potential improvements to the logic and function use, splitting it out into several cells is a very acceptable approach.

Hope that helps...

 

[ukmikeb]

Hi

Is the purpose of the ISERROR to check that the VLOOKUP returns a valid result?

In which case would the following not suffice :-

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]=IF(ISERROR(VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)),””, IF(VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)=(SUM(IF($D$2:$D$14=D3,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]IF(H3=$H$2:$H$14,IF(I3=$I$2:$I$14,$J$2:$J$14,0),0),0))-VLOOKUP(H3&IF(G3="EUR","RSAD","RSAB")&D3,A:J,10,FALSE)),"OK","CHECK"))[/FONT][/COLOR]
[/COLOR][/FONT]

Formula not checked!

Apologies if I have misinterpreted and gone along the wrong track.

hth

Mike

 

[Pistoleiro]

Hey All,

Progress, I appear to have got the array entry bit down now just to actually replace the rest of the array in there..

Almost there...

below is my code

Please note PC is worksheet

    PC.Range("O3").FormulaArray = "=IF(ISERROR(VLOOKUP(H6&IF(G6=""EUR"",""RSAD"",""RSAB"")&D6,A:J,10,FALSE)),"""",""REPLACEA"")"
    PC.Range("O3").Replace "REPLACEA", "IF(VLOOKUP(H3&IF(G3=""EUR"",""RSAD"",""RSAB"")&D3,A:J,10,FALSE)=(SUM(IF($D$2:$D$14=D3,IF(H3=$H$2:$H$14,IF(I3=$I$2:$I$14,$J$2:$J$14,0),0),0))-VLOOKUP(H3&IF(G3=""EUR"",""RSAD"",""RSAB"")&D3,A:J,10,FALSE)),""OK"",""CHECK""))"

 

[Pistoleiro]

For some reason the second line of the code doesn't work any ideas?

Thanks

 

[pgc01]

Hi

Not tested, but shouldn't it be:

PC.Range("O3").Replace """REPLACEA""", ...

also in the replace string it seems to me there's an extra closing parenthesis ")" at the end?

...,""CHECK""))"

 

[ukmikeb]

Following Colin's suggestion to breakdown the formula you'll probably find this will work -

Dim StrFm as String
Dim strVL as String
 
strFm = "=IF(ISERROR($O$11),"""",IF($O$11=(SUM(IF($D$2:$D$14=D3,IF(H3=$H$2:$H$14,IF(I3=$I$2:$I$14,$J$2:$J$14,0),0),0))-$O$11),""OK"",""CHECK""))"
strVL = "=VLOOKUP(H6&IF(G6=""EUR"",""RSAD"",""RSAB""&D6,A:J,10,FALSE)"
 
PC.Range("O11").FormulaArray = strVL
PC.Range("O3").FormulaArray = strFm

You might like to relocate the VLOOKUP from cell O11.

hth

Mike

 

[Pistoleiro]

Thanks to all of you who posted in response to my problem after many problems and generally messing about to see what works I finally managed to get the thing to work with replace don't know what I did there but I believe PGC01 may have been spot on when he mentioned a misplaced ". After almost 5 hours of banging my head against the keyboard. it turns out the error was only a PEBKAC

Thanks guys and for those that need it for their own purposes please find the below working code

Cheers all for your prompt responses ray: and for your interesting suggestions. The shortening method may have worked I eventually managed to rework the formula to 279 characters long but excel crashed so I lost that gem. Atleast now it's working.

What a mission

PC.Range("O3").FormulaArray = "=IF(VLOOKUP(H3&IF(G3=""EUR"",""RSAD"",""RSAB"")&D3,A:J,10,FALSE)="""","""",""X_X_X"")"
    PC.Range("O3").Replace """X_X_X""", "IF(VLOOKUP(H3&IF(G3=""EUR"",""RSAD"",""RSAB"")&D3,A:J,10,FALSE)=(SUM(IF($D$2:D9=D3,IF(H3=$H$2:H9,IF(I3=$I$2:I9,$J$2:J9,0),0),0))-VLOOKUP(H3&IF(G3=""EUR"",""RSAD"",""RSAB"")&D3,A:J,10,FALSE)),""OK"",""CHECK"")"