Trouble using array formula in a macro

[brightyoyo]

Hi, I am trouble getting this array formula


=SUBSTITUTE(LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1)&SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1),""),",",","&LEFT(A2,MIN(IFERROR(SEARCH({"0","1","2","3","4","5","6","7","8","9"},A2),999))-1))

Form NARAYANK991


to work in this macro.


Range("D1").Select

Selection.FormulaArray = _

"=SUBSTITUTE(LEFT(RC[-3],MIN(IFERROR(SEARCH({""0"",""1"",""2"",""3"",""4"",""5"",""6"",""7"",""8"",""9""},RC[-3]),999))-1)&SUBSTITUTE(RC[-3],LEFT(RC[-3],MIN(IFERROR(SEARCH({""0"",""1"",""2"",""3"",""4"",""5"",""6"",""7"",""8"",""9""},RC[-3]),999))-1),""""),"","","",""&LEFT(RC[-3],MIN(IFERROR(SEARCH({""0"",""1"",""2"",""3"",""4"",""5"",""6"",""7"",""8"",""9""},RC[-3]),999))-1))"

Range("D1").Select

Selection.Copy

Range("D2:D52").Select

ActiveSheet.Paste

 

[shrivallabha]

It is the length of formula and FormulaArray character limitation which is giving you problems.


One way.

1. Goto Formula Ribbon | Defined Names | Define Name.

2. Define a name 'NumArray' and in its 'Refers to' option put:

={"0","1","2","3","4","5","6","7","8","9"}


Now the formula reduces down to:

=SUBSTITUTE(LEFT(A2,MIN(IFERROR(SEARCH(NumArray,A2),999))-1)&SUBSTITUTE(A2,LEFT(A2,MIN(IFERROR(SEARCH(NumArray,A2),999))-1),""),",",","&LEFT(A2,MIN(IFERROR(SEARCH(NumArray,A2),999))-1))


or in VBA:

[pre]

Range("D2").FormulaArray = _
"=SUBSTITUTE(LEFT(RC[-1],MIN(IFERROR(SEARCH(NumArray,RC[-1]),999))-1)&SUBSTITUTE(RC[-1],LEFT(RC[-1],MIN(IFERROR(SEARCH(NumArray,RC[-1]),999))-1),""""),"","","",""&LEFT(RC[-1],MIN(IFERROR(SEARCH(NumArray,RC[-1]),999))-1))"

[/pre]
And then it works as you'd want!

 

[brightyoyo]

Thanks shrivallabha it work perfectly.