Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Replace part of formula in ArrayFormula

  1. #1
    Board Regular NicholasP's Avatar
    Join Date
    Nov 2006
    Location
    Juneau, AK
    Posts
    276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Replace part of formula in ArrayFormula

    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
    If it's not cloudy, raining, blowing, or snowing...it's threatening to get cloudy, rain, blow or snow.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,305
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    Try using the A1 reference style, instead of R1C1...

  3. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,301
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    ... 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.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    Should the second part of the formula be :-
    Code:
    theFormulaPart2 = "MATCH(RC[-5]&""AY70""&"," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"
    hth
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  5. #5
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,301
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    Hi Mike

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

    To understand recursion, you must understand recursion.

  6. #6
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    @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.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,305
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    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:
    Sub test()
    
        Dim theFormulaPart1 As String
        Dim theFormulaPart2 As String
        
        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'"
        
        With Range("H2")
            .FormulaArray = theFormulaPart1
            .Replace "X_X_X", theFormulaPart2
        End With
    
    End Sub

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,301
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    Quote Originally Posted by ukmikeb View Post
    @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 by pgc01; Jul 27th, 2015 at 05:29 AM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    Board Regular NicholasP's Avatar
    Join Date
    Nov 2006
    Location
    Juneau, AK
    Posts
    276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Replace part of formula in ArrayFormula

    Quote Originally Posted by Domenic View Post
    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:
    Sub test()
    
        Dim theFormulaPart1 As String
        Dim theFormulaPart2 As String
        
        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'"
        
        With Range("H2")
            .FormulaArray = theFormulaPart1
            .Replace "X_X_X", theFormulaPart2
        End With
    
    End Sub

    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
    If it's not cloudy, raining, blowing, or snowing...it's threatening to get cloudy, rain, blow or snow.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •