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 = "=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

    Code
    [/FONT]Dim Index1 As VariantDim Index2 As VariantIndex1 = "=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)”



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


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


    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!