Excel VBA .Replace not replacing text

Question

I am working with a long array formula, and so i am replacing the condition with "X_X" to avoid the 255 character limit. i also have a predefined variable qbb with the actual length of the array being checked, though for this example, i just set it to 150. The full formula itself works fine when i add it manually. The issue is that when i try to do the replacement via VBA, the ".Replace" is not replacing anything. the end result still has the "X_X" and the "114"

    Dim frmla As String
    Dim qbb As Variant
    qbb = 150
    frmla = "('Final Summary'!R2C2:R114C2<=R13C4)*('Final Summary'!R2C2:R114C2>=R14C4)*(('Final Summary'!R2C3:R114C3=R10C4)+(R10C4=""""))"
    With ActiveSheet.Range("C16")
        .FormulaArray = "=IFERROR(INDEX('Final Summary'!R2C1:R114C1,SMALL(IF(X_X,ROW('Final Summary'!R2C1:R114C1)-MIN(ROW('Final Summary'!R2C1:R114C1))+1),ROWS(R16C3:RC))),"""")"
        .Replace What:="X_X", Replacement:=frmla
        .Replace What:="114", Replacement:=qbb
    End With

I also tried the alternate ".Replace" format:

        .Replace "X_X", frmla
        .Replace "114", qbb

No luck.

Any ideas?

Since LookAt will be defaulting to whatever it was last set at, is that xlPart, or xlWhole? — Chronocidal
– Chronocidal, Commented May 5, 2021 at 10:05
in the same macro, i have a previous Find with Lookat:=xlWhole — Bobert1234
– Bobert1234, Commented May 5, 2021 at 10:07
Do so. To replace part of the string, like you want to, it needs to be xlPart — Chronocidal
– Chronocidal, Commented May 5, 2021 at 10:09
interesting. now it replaces the 114 with 150, but leaves the X_X — Bobert1234
– Bobert1234, Commented May 5, 2021 at 10:12

Axel Richter · Accepted Answer · 2021-05-05 10:45:57Z

The Range.Replace needs at least LookAt to be set because the settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. So LookAt might be xlWhole from saved values.

And Range.Replace runs after the range contains the formula already. But then this formula will be in A1 format and not in R1C1 format when default Excel settings are used. So the frmla also needs to be in A1 format and not in R1C1 format.

Dim frmla As String
Dim qbb As Variant
qbb = 150
'frmla = "('Final Summary'!R2C2:R114C2<=R13C4)*('Final Summary'!R2C2:R114C2>=R14C4)*(('Final Summary'!R2C3:R114C3=R10C4)+(R10C4=""""))"
frmla = "('Final Summary'!$B$2:$B$114<=$D$13)*('Final Summary'!$B$2:$B$114>=$D$14)*(('Final Summary'!$B$2:$B$114=$D$10)+($D$10=""""))"
With ActiveSheet.Range("C16")
    .FormulaArray = "=IFERROR(INDEX('Final Summary'!R2C1:R114C1,SMALL(IF(X_X,ROW('Final Summary'!R2C1:R114C1)-MIN(ROW('Final Summary'!R2C1:R114C1))+1),ROWS(R16C3:RC))),"""")"
    .Replace What:="X_X", Replacement:=frmla, LookAt:=xlPart, MatchCase:=True
    .Replace What:="114", Replacement:=qbb, LookAt:=xlPart, MatchCase:=True
End With

Collectives™ on Stack Overflow

Excel VBA .Replace not replacing text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related