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>

 

[DushiPunda]

Are you sure that you're getting the correct information from your index formulas? Maybe you can try changing Index1 and Index2 to Variant instead of String?

Edit...I see what you're doing. Maybe you can use the Application.WorksheetFunction.Index to actually get a value and then sum the 2 results.

 

[solti]

Below code is working

Dim Index1 As Variant
Dim Index2 As Variant

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, 13).FormulaArray = Index1

Below code is not working

Dim Index1 As Variant
Dim Index2 As Variant

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, 13).FormulaArray = “=sum(Index1, Index2)”

 

[DushiPunda]

What is the result of of both of your index formulas?

There are two problems with the code that is not working
1. You're trying to sum your two strings (Index1 & Index2) together without actually getting the result of the index formulas.
2. You're trying to set the FormulaArray in .Cells(i, 13) to the sum of something it doesn't recognize...Basically, if you have the following:

X = 1
Y = 2
.Cells(i, 13).FormulaArray = "=SUM(x, y)"

The formula inserted into .Cells(i, 13) is exactly as you type it and that formula does not know what X or Y is.

Anyway, here's some things you can try:
Index1 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))")
Index2 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))")


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

Or you can try:

Index1 = Application.WorksheetFunction.Index(index,Application.WorksheetFunction.Match(match),Application.WorksheetFunction.Match(match))
Index2 = Application.WorksheetFunction.Index(index,Application.WorksheetFunction.Match(match),Application.WorksheetFunction.Match(match))

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

So basically, get the results of your INDEX/MATCH formulas, then set the value of .Cells(i, 13) to the sum of the two. I don't believe there is any reason to be applying formulas to cells on your worksheet. Also, is there a reason you're working in R1C1?

 

[solti]

unfortunately. Non of these solution works. Its strange that single function works but added together don't
Anyway thanks for helping

What is the result of of both of your index formulas?

There are two problems with the code that is not working
1. You're trying to sum your two strings (Index1 & Index2) together without actually getting the result of the index formulas.
2. You're trying to set the FormulaArray in .Cells(i, 13) to the sum of something it doesn't recognize...Basically, if you have the following:

X = 1
Y = 2
.Cells(i, 13).FormulaArray = "=SUM(x, y)"

The formula inserted into .Cells(i, 13) is exactly as you type it and that formula does not know what X or Y is.

Anyway, here's some things you can try:
Index1 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Pła"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))")
Index2 = Evaluate("INDEX(lista!R20C2:R2000C2000,MATCH(""Hon"",lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))")


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

Or you can try:

Index1 = Application.WorksheetFunction.Index(index,Application.WorksheetFunction.Match(match),Application.WorksheetFunction.Match(match))
Index2 = Application.WorksheetFunction.Index(index,Application.WorksheetFunction.Match(match),Application.WorksheetFunction.Match(match))

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

So basically, get the results of your INDEX/MATCH formulas, then set the value of .Cells(i, 13) to the sum of the two. I don't believe there is any reason to be applying formulas to cells on your worksheet. Also, is there a reason you're working in R1C1?

 

[DushiPunda]

1. What is the expected result of your first INDEX formula?
2. What is the expected result of your second INDEX formula?

Convert your formula into VBA or use the evaluate function to get the results.

Use MsgBox's:

Index1 = ...
Index2 = ...

MsgBox "Index1: " & Index1
MsgBox "Index2: " & Index2

Or, step through your code (F8) and monitor the Locals window to see what your variables (Index1 & Index2) are set to.

If they are set to the expected values, then .Cells(i, 13).Value = Index1 + Index2 will work.

You haven't told me why they aren't working, what errors you are receiving, or anything of the such. I don't know why you're using R1C1 reference style, but if you were using A1 I'd probably be able to help you a lot more.

If you give me more information, I can probably help you. But you've been exceptionally vague thus far.

 

[DushiPunda]

Its strange that single function works but added together don't

It's really not strange if you think about what you are doing...
1. You're setting your variables to a formula which is formatted as a STRING.
2. You then take your string and, using Cells.FormulaArray, you are setting that cell's formula to the value of your variable, which is a formula...formatted as a string.

In the case of it not working:
1. You're setting your variables to a formula which is formatted as a STRING.
2. You then take your strings and, using Cells.FormulaArray, you are setting that cell's formula to the value of your first variable + the value of your second variable...which are both strings.

So let's look at this another way.

Dim X As Variant
Dim Y As Variant

X = "=SUM(A1:A3)"
Y = "=SUM(B1:B3)"

.Cells(i, 13).FormulaArray = “=SUM(X, Y)”

So, as is, this will set the formula of that specific cell to:
=SUM(X, Y)

Obviously the formula doesn't know what X or Y are. You could change it to this:

.Cells(i, 13).FormulaArray = "=SUM(" & X & "," & Y & ")"

This will set the formula of that specific cell to:
=SUM(=SUM(A1:A3),=SUM(B1:B3))

Do you see why this isn't working?

The formula you are applying to the cell is, verbatim:
=SUM(Index1, Index2)

The formula doesn't know what Index1 or Index2 are.

So, even if you changed it to:
.Cells(i, 13).FormulaArray = "=SUM(" & Index1 & "," & Index2 & ")"

The formula you apply to that cell will be:

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

That clearly isn't going to work.

So you need to, in VBA, get the result of your INDEX formulas. As I discussed before, there are a few options you can go with:
1. Evaluate
2. Convert your formula into VBA using Application.WorksheetFunction.Index/Application.WorksheetFunction.Match

Once you get the those values, then you can do one of the following:

1. .Cells(i, 13).FormulaArray = "=SUM(" & Index1 & "," & Index2 & ")"
2. .Cells(i, 13).Value = Index1 + Index2

Hopefully that makes sense.

Again, if you convert this to A1 reference style, I can help you out much more than I can with the R1C1 style.

 

[solti]

Thanks a lot for help.

I tested a code once again. You're right with variables. I didn't pay enough attention to that.
I coverted R1C1 formula to what A1 reference style and first function looks like this:

.Cells(i, 13).FormulaArray = Application.WorksheetFunction.Index(Worksheets("lista").Range("$B$20:$BXX$2000"), Application.WorksheetFunction.Match("Pła", Worksheets("lista").Range("$A$20:$A$2000"), 0), Application.WorksheetFunction.Match(.Range("B" & i) & .Range("C" & i), Worksheets("lista").Range("$B$17:$BXX$17") & Worksheets("lista").Range("$B$13:$BXX$13"), 0))

When instead of variable "i" I put for example "3" and get rid off "&" (for array formula. Probably above code is wrong as it's not working as array formula but when it's R1C1 version (below) it running). Below code is working (I tested on Immediate window)

Application.WorksheetFunction.Index(Worksheets("lista").Range("$B$20:$BXX$2000"), Application.WorksheetFunction.Match("Pła", Worksheets("lista").Range("$A$20:$A$2000"), 0), Application.WorksheetFunction.Match(Worksheets("baza1").Range("B3"), Worksheets("lista").Range("$B$17:$BXX$17"), 0))

Dim Index1 As String
Dim Index2 As String
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, 13).FormulaArray = Index1 
[\CODE]
Above code is working. But below is not
[CODE]
.Cells(i, 13).FormulaArray = "=SUM(" & Index1 & "," & Index2 & ")"
[\CODE]

I have no idea. Generaly when formula R1C1 is array with one function is OK. Adding second function results in 0 result. I recored macro, changed for a1 style. No differance. Both functions added don't work.
Maybe I could send you an example file or there is a function (not array) which can get a value based on a row and 2 columns.
Thanks again for input
s.

 

[solti]

clicking on "Evaluate formula" from the toolbar I get "Value not Available Error" which is strange because when I manually edit the formula on the sheet and confirm it with ENTER it works
Hmmm...............

 

[DushiPunda]

Thanks a lot for help.

I tested a code once again. You're right with variables. I didn't pay enough attention to that.
I coverted R1C1 formula to what A1 reference style and first function looks like this:

.Cells(i, 13).FormulaArray = Application.WorksheetFunction.Index(Worksheets("lista").Range("$B$20:$BXX$2000"), Application.WorksheetFunction.Match("Pła", Worksheets("lista").Range("$A$20:$A$2000"), 0), Application.WorksheetFunction.Match(.Range("B" & i) & .Range("C" & i), Worksheets("lista").Range("$B$17:$BXX$17") & Worksheets("lista").Range("$B$13:$BXX$13"), 0))

When instead of variable "i" I put for example "3" and get rid off "&" (for array formula. Probably above code is wrong as it's not working as array formula but when it's R1C1 version (below) it running). Below code is working (I tested on Immediate window)

Application.WorksheetFunction.Index(Worksheets("lista").Range("$B$20:$BXX$2000"), Application.WorksheetFunction.Match("Pła", Worksheets("lista").Range("$A$20:$A$2000"), 0), Application.WorksheetFunction.Match(Worksheets("baza1").Range("B3"), Worksheets("lista").Range("$B$17:$BXX$17"), 0))

Dim Index1 As String
Dim Index2 As String
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, 13).FormulaArray = Index1 
[\CODE]
Above code is working. But below is not
[CODE]
.Cells(i, 13).FormulaArray = "=SUM(" & Index1 & "," & Index2 & ")"
[\CODE]

I have no idea. Generaly when formula R1C1 is array with one function is OK. Adding second function results in 0 result. I recored macro, changed for a1 style. No differance. Both functions added don't work.
Maybe I could send you an example file or there is a function (not array) which can get a value based on a row and 2 columns.
Thanks again for input
s.[/QUOTE]

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:
[code]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