Replace part of formula in ArrayFormula

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
291
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try using the A1 reference style, instead of R1C1...
 
Upvote 0
... 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.
 
Upvote 0
Should the second part of the formula be :-
Rich (BB code):
theFormulaPart2 = "MATCH(RC[-5]&""AY70""&"," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"

hth
 
Upvote 0
Hi Mike

What would then be the first parameter of the Match()? How would you interpret it?
 
Upvote 0
@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.
 
Upvote 0
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]
 
Upvote 0
@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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top