• 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.

The VBA code "Replace" does not replace any text in FormulaArray ( SOLVED )

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!
 
LePrinceCeleste
You should send a sample Excel-file,
which shows Your challenge
as well as
expected results with Your sample data.

Hello vletm, thank you for your time.

Please find attached the sample file you asked for.

My matrix formula works very well and gives me the right answers when it is inserted manually (see cell 'J4').

In this case, on the other hand, I want a Macro to insert it in cell 'J1' at some point.
However, since it's a matrix formula and the number of characters exceeds the 256 allowed, then I decided to get around this limitation.

So I first shortened the formula by replacing one piece with the number 1111, then replacing that number using the "replace" function with the missing piece.
>>> And THAT'S what I can't do.
>>> THAT'S my problem.
The macro runs fine, without giving any errors, but no replacement was done.
 

Attachments

  • 2021-07-08jeu171800 - FormulaArray - Sample File.xlsm
    23 KB · Views: 1
Last edited:
PROBLEM SOLVED!!!

My VBA code lineMonth was correct with only ONE flaw (as usual) :
  • The cell references were in R1C1!!!
  • While they should be in A1!!!
Below is the image of the correct code:

75259

In red, the code as it was before and in black, the code as it should be.

Conclusion:
>> One of the rules to respect for the "replace" function to work: The text to replace must be in ENGLISH and in A1.

ONCE AGAIN, THANK YOU FOR THE TIME TAKEN TO HELP ME SOLVE MY PROBLEM.
I wish you all the best for you and all your loved ones!
 
Back
Top