# 255 character limit with VBA array formula

• Hello.

Could you please help.

Below code is working

Code
``[/FONT]Dim Index1 As VariantDim Index2 As VariantIndex1 = &quot;=INDEX(lista!R20C2:R2000C2000,MATCH(&quot;&quot;Pła&quot;&quot;,lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))&quot;Index2 = &quot;=INDEX(lista!R20C2:R2000C2000,MATCH(&quot;&quot;Hon&quot;&quot;,lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))&quot;.Cells(i, 13).FormulaArray = Index1``

Below code is not working

Code
``[/FONT]Dim Index1 As VariantDim Index2 As VariantIndex1 = &quot;=INDEX(lista!R20C2:R2000C2000,MATCH(&quot;&quot;Pła&quot;&quot;,lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))&quot;Index2 = &quot;=INDEX(lista!R20C2:R2000C2000,MATCH(&quot;&quot;Hon&quot;&quot;,lista!R20C1:R2000C1,0),MATCH(RC2&RC3,lista!R17C2:R17C2000&lista!R13C2:R13C2000,0))&quot;.Cells(i, 13).FormulaArray = &ldquo;=sum(Index1, Index2)&rdquo;``

s.

• Re: 255 character limit with VBA array formula

just a guess, but shouldnt it be:

Code
``.Cells(i, 13).FormulaArray = “=sum(" & Index1 & "," & Index2 & ")”``

Also, what error are you getting?

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: 255 character limit with VBA array formula

unfortunately gives me 0.

I tried to check via Immediate window but don't know how (for array formula)
Anyway thanks for help

s.

• Re: 255 character limit with VBA array formula

Well, its not a compile error... so maybe zero is the correct answer for that SUM formula. I wouldnt be able to debug it any further without seeing a sample of your data. When the formula is in the cell... highlight the cell and click on "Evaluate formula" from the toolbar.

Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

_______________________________________________

• Re: 255 character limit with VBA array formula

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...............

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!