• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA .FormulaArray and .Replace Function

Shake728

New Member
I am trying to use the .replace function to create a longer than 255 character vba formula. I am having issues with the replace portion not working. Would anyone be able to quickly look to see what I am doing wrong?

Code:
Sub Macro8()
'
' Macro8 Macro
'

'
Dim F1P1 As String
Dim F1P2 As String
Dim F1P3 As String

    F1P1 = "=IF(OR(EOMONTH(Current_Month_End,0)<EOMONTH(INDIRECT(""'""&R40C&""'!Termination_Extension_Date""),0),INDIRECT(""'""&R40C&""'!Termination_Extension_Date"")=0),YYYY,0)"
    F1P2 = """INDEX(INDIRECT(""'""&R[-9]C&""'!$A$29:$K$1098""),MATCH(1,IF(INDIRECT(""'""&R[-9]C&""'!$f$29:$f$1098"")<>"""",1,FALSE),0),6)"""

With ThisWorkbook.Sheets("Summary Workpaper").Range("C48")
                    .FormulaArray = F1P1
                    .Range("C48").Replace What:=F1P2, Replacement:=F1P2, LookAt:=xlPart
                    
End With


End Sub
 

Shake728

New Member
*Updated code

Code:
Sub Macro8()
'
' Macro8 Macro
'

'
Dim F1P1 As String
Dim F1P2 As String
Dim F1P3 As String

    F1P1 = "=IF(OR(EOMONTH(Current_Month_End,0)<EOMONTH(INDIRECT(""'""&R40C&""'!Termination_Extension_Date""),0),INDIRECT(""'""&R40C&""'!Termination_Extension_Date"")=0),YYYY,0)"
    F1P2 = """INDEX(INDIRECT(""'""&R[-9]C&""'!$A$29:$K$1098""),MATCH(1,IF(INDIRECT(""'""&R[-9]C&""'!$f$29:$f$1098"")<>"""",1,FALSE),0),6)"""

With ThisWorkbook.Sheets("Summary Workpaper").Range("C48")
                    .FormulaArray = F1P1
                    .Range("C48").Replace What:="YYYY", Replacement:=F1P2, LookAt:=xlPart
                    
End With


End Sub
 

LePrinceCeleste

New Member
Hello @all!

To get around the 256 character limitation of FormulaArray, I tried the trick below to find my full array formula:

Sub test()

Dim ligneMois
ligneMois = "(IFERROR(MONTH('Rest. (Total)'!R1C1:R999C1),0)=MONTH(R1C9))*(IFERROR(YEAR('Rest. (Total)'!R1C1:R999C1),0)=YEAR(R1C9))*ROW('Rest. (Total)'!R1:R999)"

With Range("J1")
.FormulaArray = _
"=IFERROR(IF(SUMPRODUCT(1111)=0,0," & Chr(10) & _
"INDEX('Rest. (Total)'!R1C1:R999C5,SUMPRODUCT(1111)" & _
",COLUMN('Rest. (Total)'!R1C1))),0)" & _
""
.Replace What:="1111", Replacement:=ligneMois, LookAt:=xlPart
End With
End Sub

Everything goes fine until the end without error message, but the replacement was not done as requested.

Can you please help me?

In advance a big THANKS to all who will take a time for my problem!
 

Debaser

Well-Known Member
Unless your Excel is set to use R1C1 format, you should put the ligneMois part of the formula in A1 format.
 
Top