Replace part of formula in ArrayFormula

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
287
So I've been working on a longer array formula and I feel like I've got it down pretty well as far as what other people on MrExcel have suggested, but I'm falling a little short. The code runs without error now, but the .Replace portion of the code isn't working. Does anyone have any thoughts?? Any help would be greatly appreciated

I'm trying to execute the suggestion offered here (that has been suggested several times to this question on MrExcel):

Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA

Thanks
Nick

Code:
Sub test()

Range("H2").Select
    
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim AA As String


AA = "'[2015 July 20_Daily Fantastical Supplement.xlsm]XYZ Fantastical Supplement'"

theFormulaPart1 = "=INDEX(" & AA & "!R145C3:R10000C3," & "X_X_X)"
theFormulaPart2 = "MATCH(RC[-5]&""AY70""," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"


With Selection
    .FormulaArray = theFormulaPart1
    .Replace "X_X_X)", theFormulaPart2  ''''<<---this portion is not working
End With

End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
... or if you want to use the R1C1 notation, I don't understand the part2 of the formula:

Code:
theFormulaPart2 = "MATCH(RC[-5]&""AY70""," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"
what is the first parameter of the Match()?

Code:
 ... RC[-5]&""AY70""
makes no sense to me.
Maybe I'm missing something?

Please clarify.
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
Should the second part of the formula be :-
Rich (BB code):
theFormulaPart2 = "MATCH(RC[-5]&""AY70""&"," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"
hth
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
Hi Mike

What would then be the first parameter of the Match()? How would you interpret it?
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
@pgc01
I would suspect that there is a concatenation of the cell contents and "AY70" which is matched against the concatenation of "Sheet Reference"!R145C11:R10000C11&" and "Sheet Reference"!R145C10:R10000C10".

So, it would appear that possibly there is another stray ampersand at the end of the first range reference, which should be removed.

But, I could be wrong.

And, with the Replace, I think the OP should just be replacing the "X_X_X" characters and not including the closing bracket. In which case the OP needs to remove the second closing bracket from theFormulaPart2.

Having looked at the quoted DailyDose I can see where the OP gets that idea.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
18,919
It looks like the syntax is correct in the original code because if you use the A1 reference style instead it seems to work. I think the reason the original code didn't work is that initially when the first part of the formula is entered into the cell it results in the cell containing an A1 reference style formula. Then you're trying to replace part of the A1 reference style formula with a R1C1 style reference. So if you want to use the R1C1 style instead of A1, I think the following should work...

Code:
[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] theFormulaPart1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] theFormulaPart2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    theFormulaPart1 = "=INDEX(X_X_X!R145C3:R10000C3,MATCH(RC[-5]&""AY70"",X_X_X!R145C11:R10000C11&X_X_X!R145C10:R10000C10,0))"
    theFormulaPart2 = "'[2015 July 20_Daily Fantastical Supplement.xlsm]XYZ Fantastical Supplement'"
    
    [COLOR=darkblue]With[/COLOR] Range("H2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X", theFormulaPart2
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,732
@pgc01
I would suspect that there is a concatenation of the cell contents and "AY70" which is matched against the concatenation of "Sheet Reference"!R145C11:R10000C11&" and "Sheet Reference"!R145C10:R10000C10".
Hi Mike

The way I see it, you cannot concatenate anything with AY70 (if that's the reference of a cell) because AY70 does not exist in R1C1 notation.

You could do it if you were using A1 notation.

Excel does not allow you to use something like

R1C4&$B$6

mixing the 2 notations.

In the code, Nicholas is using R1C1 notation and so all the formula must respect that notation.

Unless he does not mean the cell AY70 but, instead, the string "AY70", that could be part of a some ID. In that case that would be OK.

Let's see if the OP can clarify.
 
Last edited:

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
287
It looks like the syntax is correct in the original code because if you use the A1 reference style instead it seems to work. I think the reason the original code didn't work is that initially when the first part of the formula is entered into the cell it results in the cell containing an A1 reference style formula. Then you're trying to replace part of the A1 reference style formula with a R1C1 style reference. So if you want to use the R1C1 style instead of A1, I think the following should work...

Code:
[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] theFormulaPart1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] theFormulaPart2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    theFormulaPart1 = "=INDEX(X_X_X!R145C3:R10000C3,MATCH(RC[-5]&""AY70"",X_X_X!R145C11:R10000C11&X_X_X!R145C10:R10000C10,0))"
    theFormulaPart2 = "'[2015 July 20_Daily Fantastical Supplement.xlsm]XYZ Fantastical Supplement'"
    
    [COLOR=darkblue]With[/COLOR] Range("H2")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X", theFormulaPart2
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

You got it exactly. AY70 was not a cell reference; I should've changed that so as to avoid any confusion. I appreciate everyone's input on this!!

Thanks again to everyone who took time to take a look and offer input!

Nick
 

Forum statistics

Threads
1,077,778
Messages
5,336,248
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top