VBA formula array over 255 characters

[pablo_max3045]

Hello all,

I know that you cannot have an array over 255 characters when using VBA (for whatever reason) and that the solution is to split the formula into two variables.
Then, use a replace function on some random characters in the first formula with the 2nd variable.

However, I have no luck at all with it.
This is my code until now.

Sub Macro10()

Dim formula1 As String
Dim formula2 As String


formula1 = "=IFERROR(INDEX(Comp_Against!R2C2:R2569C8,MATCH(New_Plan!RC2&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0),1),""REMOVE"")"
formula2 = ",IFERROR(INDEX(Comp_Against!R2C2:R2569C8,MATCH(New_Plan!RC13&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0),1),""no"")))"


    Range("N2").Select
    With ActiveSheet.Range("N2")
    .FormulaArray = formula1
    .Replace "REMOVE", formula2
    End With
    
        

'working formula when copied into excel minus R1C1 notation
'"=IFERROR(INDEX(Comp_Against!R2C2:R2569C8,MATCH(New_Plan!RC2&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0),1),IFERROR(INDEX(Comp_Against!R2C2:R2569C8,MATCH(New_Plan!RC13&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0),1),""no""))"


End Sub

The first bit gets pasted in there, but the replacement doesnt happen.
I am sure I am doing something basic wrong, but I just dont know what it would be.

I actually need to replace [""REMOVE"")] and then stick the second part on, but excel complains if i try to search for that.

 

[Jonmo1]

Since the range indexed is the same regardless of which match works, You don't have to test the INDEX for error.
Only the the MATCH

i.e. Instead of
IFERROR(INDEX(MATCH(firstmatch)),IFERROR(INDEX(MATCH(2ndmatch)),"no")))

Try
IFERROR(INDEX(IFERROR(MATCH(firstmatch),MATCH(2ndmatch))),"no")


This is fewer than 255 characters

=IFERROR(INDEX(Comp_Against!R2C2:R2569C2,IFERROR(MATCH(New_Plan!RC2&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0),MATCH(New_Plan!RC13&New_Plan!RC1,Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1,0))),""no"")

 

[pablo_max3045]

hmm, for some reason my replies using quick reply are not showing up.

Thanks for the hint. I will try this. I fear that by adding the last row variable it will be over. normally instead of R1981C1 i would use R["& lstr &"] to shrink the size of the search area for speed reasons.
I guess I should change the lstr to something like "i".
Is it possible to use a shorter sheet name?
In other parts of the macro, i have defined nTP and oTP to be New_Plan and Comp_Against, but if I try those shortcuts, vba would insert "oTP" into the formula. like oTP!RC:C2.....

 

[Jonmo1]

Variables for row #s should not be a problem.

What matters is the character length of the resulting text string, not the chracter length of the vba code.

 

[pablo_max3045]

got it. Thanks.
For formula did work. I was able to just change the index array a bit and then set the column returned at the end, since I need to return 3 other cells if the match is found.
So.. everything seems to work. It is amazing slow, but it works.
The data set is about 15k lines and 15 columns in each sheet.
I am thinking it may be faster, in terms of processing to make two helper columns with the concatenated search string and then use vlookup to find the match.

 

[Jonmo1]

You're welcome.

I am thinking it may be faster, in terms of processing to make two helper columns with the concatenated search string and then use vlookup to find the match.

Yes, that would be immensely more efficient.
But you don't need to do both datasets.
Just the one in the lookup table
Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1

You can still concatenate the lookup value in the formula, no biggy.

 

[pablo_max3045]

You're welcome.



Yes, that would be immensely more efficient.
But you don't need to do both datasets.
Just the one in the lookup table
Comp_Against!R2C2:R1707C2&Comp_Against!R2C1:R1918C1

You can still concatenate the lookup value in the formula, no biggy.

It was. I decided to do it in that way. Your advice on the formula worked fine though. Just took a
bit too long.