CODEWITHSUNDEEP
vbadynamicstaticarray-formulas
Diego Ribba
Diego Ribba

Reputation: 140

Can't make Replace method work with Array Formula (VBA)

I have looked for a solution to enter a long array formula, but replace method isn't working. this is my code:

Sub code()
Dim parte1, parte2 As String
With ThisWorkbook.Sheets("RESUMEN E. CRITICOS")
    ''RUT
        parte1 = "IFERROR(INDEX(rut_cliente,SMALL(IF(plataforma=R1C2,ROW(rut_cliente)-ROW(BD_EV!R3C1)+1),ROWS(BD_EV!R2C1:R[-1]C[-2]))),"""")"
        With .Range("C3")
            .FormulaArray = "=IF(R1C2=""Todas"",IFERROR(INDEX(rut_cliente,SMALL(IF(plataforma<>""*"",ROW(rut_cliente)-ROW(BD_EV!R3C1)+1),ROWS(BD_EV!R2C1:R[-1]C[-2]))),""""),reemplazar)"
            .Replace "reemplazar", parte1, xlPart
        End With
End With
End Sub

This array formula brings a list of values from another sheet and works with named ranges, but also with static references. The problem is that if I want to delete rows on the referenced sheet, the static references will stop working. I tried using a workaround with named ranges for the static references but, that didn't help(they constantly re-arrange the referenced range... cant tell why). This is the static references are: ROW(BD_EV!R3C1)+1) and ROWS(BD_EV!R2C1:R[-1]C[-2]). Please help! Thanks!

Upvotes: 0

Views: 480

Answers (1)

KyloRen
KyloRen

Reputation: 2741

This will replace reeplazar in your string and then all you have to do is make sure you assign it as an array formula when you assign it to a cell. I am trusting that your formula works as I can't test it.

But this code will do the replace for you

Sub code()
Dim parte1, parte2 As String
With ThisWorkbook.Sheets("RESUMEN E. CRITICOS")
    ''RUT

        parte1 = "IFERROR(INDEX(r‌​ut_cliente,SMALL(IF(‌​plataforma=R1C2,ROW(‌​rut_cliente)-ROW(BD_‌​EV!R3C1)+1),ROWS(BD_‌​EV!R2C1:R[-1]C[-2]))‌​),""""))"

        rep = "=IF(R1C2=""Todas"",IFERROR(INDEX(rut_cliente,SMALL(IF(plataforma<>""*"",ROW(rut_cliente)-ROW(BD_EV!R3C1)+1),ROWS(BD_EV!R2C1:R[-1]C[-2]))),""""),reemplazar)"
        arrayFormula = Replace(rep, "reemplazar", parte1)
        With .Range("C3")
            .FormulaArray = arrayFormula
        End With
End With
End Sub

Or if you wanted to get the value from the cell itself,

Sub code()
Dim parte1, parte2 As String
With ThisWorkbook.Sheets("RESUMEN E. CRITICOS")
    ''RUT

        parte1 = "IFERROR(INDEX(rut_cliente,SMALL(IF(plata‌​forma<>""*"",ROW(rut‌​_cliente)-ROW(BD_EV!‌​$A$3)+1),ROWS(BD_EV!‌​$A$2:A2))),""""),IFE‌​RROR(INDEX(rut_clien‌​te,SMALL(IF(platafor‌​ma=$B$1,ROW(rut_clie‌​nte)-ROW(BD_EV!$A$3)‌​+1),ROWS(BD_EV!$A$2:‌​A2))),""""))"
        cel = .Range("C3").FormulaArray

        arrayFormula = Replace(cel, "reemplazar", parte1)
        .Range("C3").FormulaArray = arrayFormula

End With
End Sub

Upvotes: 1

Related Questions