Enter array formula to cell - over 255 characters - Error 1004 ArrayFormula VBA

[Celticfc]

Hi all,

I'm trying to put the below code to VBA and send it to cell A2 but it won't work. I had a look on Google that its over 255 characters so tried to split the code into two variables but would still not work.

=IFERROR(INDEX('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$A:$BX,SMALL(IF('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B=filter, ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)),ROW(1:1)),COLUMN()),"")

I've tried many things in the lines of below but would not work...

Any support is appreciated.

Sub Macro1()
'
' Macro1 Macro
'
Dim x1 As String
Dim x2 As String
   ' range("a2").select
     x1 = "=IFERROR(INDEX('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C1:C76,SMALL(IF('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/"
     x2 = "[el.xlsx]formulationsTK'!C2=filter, ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C2)),ROW(R)),COLUMN()),"""")"
 Selection.FormulaArray = _
 x1 & x2
 
End Sub

 

[pgc01]

Hi

You can enter a formula with the same structure of yours (with a syntax accepted by excel) and then use replace to reconstruct it.

For ex., looking at your formula, it has the same structure as:

=IFERROR(INDEX(111111,SMALL(IF(222222=filter,333333,ROW(1:1)),COLUMN())),"")

which is a small array formula with a correct syntax that excel will accept, where

111111 stands in place of 'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C1:C76
222222 stands in place of 'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B
333333 stands in place of ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)

You can then implement it with vba:

Sub Test()

With Range("A2")
    .FormulaArray = "=IFERROR(INDEX(111111,SMALL(IF(222222=filter,333333,ROW(1:1)),COLUMN())),"""")"
    .Replace 111111, "'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C1:C76"
    .Replace 222222, "'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B"
    .Replace 333333, "ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)"
End With
End Sub

Please try.

 

[Celticfc]

Hi

You can enter a formula with the same structure of yours (with a syntax accepted by excel) and then use replace to reconstruct it.

For ex., looking at your formula, it has the same structure as:

=IFERROR(INDEX(111111,SMALL(IF(222222=filter,333333,ROW(1:1)),COLUMN())),"")

which is a small array formula with a correct syntax that excel will accept, where

111111 stands in place of 'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C1:C76
222222 stands in place of 'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B
333333 stands in place of ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)

You can then implement it with vba:

Sub Test()

With Range("A2")
    .FormulaArray = "=IFERROR(INDEX(111111,SMALL(IF(222222=filter,333333,ROW(1:1)),COLUMN())),"""")"
    .Replace 111111, "'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!C1:C76"
    .Replace 222222, "'https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B"
    .Replace 333333, "ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)"
End With
End Sub

Please try.

Hello,

Yes I've actually managed to get around this, I wasn't aware that each condition had the be a proper formula for Excel to accept. I've used the code below, thank you again.

Sub Macro1()
'
' Macro1 Macro
'
'
   
   Dim e1 As String
   Dim e2 As String
   
     e1 = "=(INDEX('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$A:$BX,SMALL(IF('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B=filter,),)))"
     e2 = "ROW('https://wels.sharepoint.com/sites/GroupSites7/blue/Talent/Shared Documents/[el.xlsx]formulationsTK'!$B:$B)),ROW(1:1)),COLUMN()))"
Range("e18").Select
 With Selection
.FormulaArray = e1
.Replace "),)))", e2

End With
End Sub

 

[Celticfc]

p.s. this also worked great -,

just thinking, can I use the above method to enter array into multiple ranges?

i.e. I need the same code in a2:150, e2:e150, f2:j150

Is there a way to incorporate the code above in to one line so that I do not have to enter it again and again?

Many thanks.

 

[pgc01]

Hi

I'm glad you figured it out.

If you want the formula in the rows below, after the code that writes the formula in A2, use

Range("A2:A10").FillDown

 

[Celticfc]

Hi

I'm glad you figured it out.

If you want the formula in the rows below, after the code that writes the formula in A2, use

Range("A2:A10").FillDown

Hello again,

Sorry for coming back to you,

I’m trying to put the code to a2:a10, c2:c10 and au2:au50

Is there a quick way of doing this?

Thank you for your support.

 

[pgc01]

My post #5 explains how to put the formula in A2:A10. Did you try it?