255 character limit with VBA array formula

[solti]

I have a problem with 255 character limit with VBA array formula. I split my code into 2 sets of functions but doing it wrong. Could you please help

[/FONT][/COLOR][COLOR=#006600][FONT=Courier]Dim Index1 As String [/FONT][/COLOR]
[COLOR=#006600][FONT=Courier]Dim Index2 As String [/FONT][/COLOR]

[COLOR=#006600][FONT=Courier]Index1 = "=INDEX('LISTA'!R20C2:R2000C2000, MATCH(""Pła"",'LISTA'!R20C1:R2000C1,0),MATCH(R[]C2&R[]C3,'LISTA'!R17C2:R17C2000&'LISTA'!R13C2:R13C2000,0))"[/FONT][/COLOR]

[COLOR=#006600][FONT=Courier]Index2 = "=INDEX('LISTA'!R20C2:R2000C2000, MATCH( ""Hon"",'LISTA'!R20C1:R2000C1,0),MATCH(R[]C2&R[]C3,'LISTA'!R17C2:R17C2000&'LISTA'!R13C2:R13C2000,0))" [/FONT][/COLOR]
[COLOR=#006600][FONT=Courier]                [/FONT][/COLOR]
[COLOR=#006600][FONT=Courier].Cells(i, 13).FormulaArray = Application.WorksheetFunction.Sum(Index1, Index2) 
[/FONT][/COLOR][COLOR=#212121][FONT=arial]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

have a problem with 255 character limit with VBA array formula. I crashed into memory but did not work</body>

 

[ukmikeb]

Hi

Following the idea in Post#4 by @DushiPanda try inserting the problematic MATCH into a vacant column on the same row (13) :-

.Cells(i, nn).FormulaArray = "=MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0)"

where nn is the vacant column.

Then each of your formulae becomes :-

Index1 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),RCnn)")
 Index2 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),RCnn)")

where nn is the vacant column.

and your result is :-

.Cells(i, 13).Value = Index1 + Index2

hth

 

[solti]

When you get your Application.WorksheetFunction.Index/.Match working, there are a couple options you can do, I would suggest the following:

Index1 = Application.WorksheetFunction.Index/.Match#1
Index2 = Application.WorksheetFunction.Index/.Match#2

.Cells(i, 13).Value = Index1 + Index2

You can send me an example file (suggest dropbox.com). I'm at work right now so I won't be able to download it and have a look at it until I'm not at work.

Alternatively, you can use two unused cells to do something like so:

Index1 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))"
Index2 = "=INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))"

.Cells(i, 75).FormulaArray = Index1
.Cells(i, 76).FormulaArray = Index2

.Cells(i, 13).Value = .Cells(i, 75).Value + .Cells(i, 76).Value

.Cells(i, 75).ClearContents
.Cells(i, 76).ClearContents

GENIUS !!!!!!!!!!!!!!!!!!
Thanks a lot, working. I owe you big beer !!!!!!!!!!!!!!!!