Array Formula and Column Error

[Dossfm0q]

The Formula Array work with Row() Ok, and Has error with Column() ! please help

Sub Rw()

    Range("A16:L40").[B][COLOR=#006400]FormulaArray[/COLOR] [/B]= "=IFERROR(MID(A1,FIND(""|"",SUBSTITUTE("",""&A1&"","","","",""|"",(ROW()-ROW(B$3)+1))),FIND("","",A1&"","",FIND(""|"",SUBSTITUTE("",""&A1&"","","","",""|"",(ROW()-ROW(B$3)+1))))-FIND(""|"",SUBSTITUTE("",""&A1&"","","","",""|"",(ROW()-ROW(B$3)+1)))),"""")"
End Sub

Get Err Mesage
unable to set the Array Formula property of the Range Class

Sub Col()
Range("A2:z118").Clear
    Range("A16:L40").[COLOR=#ff0000]FormulaArray[/COLOR] = "=MID($A1,FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))),FIND("","",$A1&"","",FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))))-FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))))"




End Sub

 

[Fluff]

You have exceeded the 255 character limit for an array formula.
Have a look here for a workround https://www.mrexcel.com/forum/excel-questions/687125-long-array-formulas-vba.html

 

[Dossfm0q]

thank you too much

 

[Fluff]

You're welcome

 

[Dossfm0q]

it is works Manuela By Press Ctr+Shift and Entr, is there any way to do like this such as send Keys.

 

[Fluff]

I've never used send keys as it's unreliable, so don't know.
Why not just the technique shown by pgc01 in the thread I linked to?

 

[Dossfm0q]

Hello here I have found answer for my problem. I have searched internet and get correct solution with <code style="margin: 0px; padding: 1px 5px; border: 0px; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap; color: rgb(36, 39, 41);">.Replace</code> function. JUST I COPY AND PASTE

Sub Col()


    [A1] = "1,2,3,4"
   With Range("B1:E1")
        ' RPLACE TO ROW
         .FormulaArray = Replace("=MID($A1,FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))),FIND("","",$A1&"","",FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))))-FIND(""|"",SUBSTITUTE("",""&$A1&"","","","",""|"",(COLUMN()-COLUMN($B1)+1))))", "(COLUMN()-COLUMN($B1)+1)", "(ROW()-ROW($B1)+1)")
          'RETURN TO ORIGINAL
          .Replace What:="ROW", Replacement:="COLUMN", LookAt:=xlPart
    End With


End Sub