0

I have spent a long time trying to figure this out ! I would appreciate it if someone could give it a second glance to see where is my mistake ! The formula does not seem to do the replacement

Here is my code

Sub remove()
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String

theFormulaPart1 = "=IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(RIGHT(Census!$BY2,4),LEFT(Census!$BY2,2),MID(Census!$BY2,4,2))-DATE(RIGHT(Census!$BM2,4),LEFT(Census!$BM2,2),MID(Census!$BM2,4,2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),""X()"")"
theFormulaPart2 = "IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),"""")"

With ActiveSheet.Range("CD2")
.FormulaArray = theFormulaPart1
.Replace """X()"")", theFormulaPart2
End With
Community
  • 1
  • 1
exlover
  • 69
  • 1
  • 2
  • 8
  • 2
    Can you not split the formula into, IDK, 3-4 columns? – Mathieu Guindon Mar 04 '16 at 22:42
  • you know: `...DATE(YEAR(Census!$BM2),MONTH(Census!$BM2),DAY(Census!$BM2))...` is the same like `INT(Census!$BM2)`... and if there is no time, then it will be like `Census!$BM2`... just the whole part: `DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))` is just `Census!$BY2-Census!$BM2` to my eye... – Dirk Reichel Mar 05 '16 at 02:42
  • @DirkReichel You are right. I was immersed in trying to fix the replace error .. that's why I didn't even spend time to make my formula smarter lol .. that's for the valuable input – exlover Mar 07 '16 at 16:22

2 Answers2

0

OK, thanks @Dirk its turn out 255 character not the issue

Second glances:

  • Formula too long, spit it to make it readable in the future
  • Use Replace function that directly process a string
  • No need to use FormulaArray if just one cell
  • Replaced string should be "X()" not "X()")

Here is the second glance code:

Sub remove()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String

    theFormulaPart1 = "=IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(RIGHT(Census!$BY2,4),LEFT(Census!$BY2,2),MID(Census!$BY2,4,2))-DATE(RIGHT(Census!$BM2,4),LEFT(Census!$BM2,2),MID(Census!$BM2,4,2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),""X()"")"
    theFormulaPart2 = "IFERROR(INDEX(Curve!D:D,MATCH(1,(DATE(Year(Census!$BY2),Month(Census!$BY2),Day(Census!$BY2))-DATE(Year(Census!$BM2),Month(Census!$BM2),Day(Census!$BM2))=Curve!$A:$A)*(Census!$T2=Curve!$C:$C),0)),"""")"


    theFormulaPart1 = Replace(theFormulaPart1, """X()""", theFormulaPart2)

    ActiveSheet.Range("CD2") = theFormulaPart1

End Sub

Another tips:

  • Avoid using ActiveSheet, how?
Community
  • 1
  • 1
Susilo
  • 816
  • 8
  • 17
  • i do not get the point of this "answer"... you can put strings longer than 255 chars in formulas with it. just test it: `selection.formulaarray = "=""aaaaaaaaaaaaaaaaa"""` and then `selection.replace "a", "bbbbbbbbbbbbbbbbb""&""b"` you will get an array formula longer than 255 chars... – Dirk Reichel Mar 05 '16 at 03:25
  • Really? I'll check that..I must be assumed *it cant be longer than 255* because of the question.. – Susilo Mar 05 '16 at 03:29
  • it sure can. but you can't directly input a string as formula (or arrayformula) which is longer via vba (you still can do manually)... and it is just that `range.replace` is not restricted to it. (like the replace tool in excel -> ctrl + H) ;) – Dirk Reichel Mar 05 '16 at 03:32
0

the error is simple:

.Replace """X()"")", theFormulaPart2

will not work cus the formula after will not be valid. you need:

.Replace """X()""", theFormulaPart2

;)

Dirk Reichel
  • 7,989
  • 1
  • 15
  • 31
  • This solution worked smoothlhy. It took me long time to try to figure where my mistake was.. but u saved me – exlover Mar 07 '16 at 16:21