Find Replace #N/A with Formula

[mylove0mylife]

Good afternoon,

In a particular column I'm trying to find all the "N/A"s and replace it with a formula. Is there a way to do this that won't get me a mistmatch error?

The error is occurring starting at "Selection.Replace" and ending at "ReplaceFormat:=False".

Sub Step_3()
    Range("M2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
  
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
    Selection.Replace What:="#N/A", Replacement:="=IF(AND(R[-4]C[11]<>"""",R[-4]C[11]<>""Hospitalist""),VLOOKUP(R[-4]C[11],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(AND(R[-4]C[13]<>"""",R[-4]C[13]<>""Hospitalist""),VLOOKUP(R[-4]C[13],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(R[-4]C[12]<>"""",VLOOKUP(R[-4]C[12],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(R[-4]C[-1]<>"""",VLOOKUP(R[-4]C[-1],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),""Unknown""))))", Lookat:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Here is the formula without R1C1 references, with pipe delimited (that's what I work in)

=IF(AND(Y34<>""|Y34<>"Hospitalist")|VLOOKUP(Y34|'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E|2|0)|IF(AND(AA34<>""|AA34<>"Hospitalist")|VLOOKUP(AA34|'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E|2|0)|IF(Z34<>""|VLOOKUP(Z34|'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E|2|0)|IF(M34<>""|VLOOKUP(M34|'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E|2|0)|"Unknown"))))

The formula works appropriately, when I enter it manually or paste it manually, but I'm having issues with VBA replicating this step. Is it because the formula isn't a String? How could I tell it (if this method won't work) to go down until you find an "N/A" and place this formula there? There is a different number of rows each time this is ran, but it will always be column N with the formula.

Thank you for any help. I'm still new to VBA, so I appreciate any guidance.

 

[GlennUK]

For experimental purposes, why don't you loop through each cell of the range, and where necessary set the FormulaR1C1 formula to your required one? And see what happens.

 

[Fluff]

If you have changed your system setting to use a pipe as the delimiter, then you need to use the pipe rather than a comma in your formula string.

 

[mylove0mylife]

If you have changed your system setting to use a pipe as the delimiter, then you need to use the pipe rather than a comma in your formula string.

I've always been in pipe, but it always records in comma and works fine in comma with VBA. When I put PIPE it does't work unfortunately.

 

[mylove0mylife]

For experimental purposes, why don't you loop through each cell of the range, and where necessary set the FormulaR1C1 formula to your required one? And see what happens.

Can you help me with the code for that? I don't think I've ever done a loop before.

 

[GlennUK]

Something like:

For Each c In Range(Range("M2"), Range("M" & Rows.Count).End(xlUp))

    If IsError(c.Value) Then ' if an error ... assumes #N/A

        c.FormulaR1C1 = _
"=IF(AND(R[-4]C[11]<>"""",R[-4]C[11]<>""Hospitalist""),VLOOKUP(R[-4]C[11],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(AND(R[-4]C[13]<>"""",R[-4]C[13]<>""Hospitalist""),VLOOKUP(R[-4]C[13],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(R[-4]C[12]<>"""",VLOOKUP(R[-4]C[12],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),IF(R[-4]C[-1]<>"""",VLOOKUP(R[-4]C[-1],'[Campaign_Maps and Data Tables.xlsx]Data Dictionary'!$D:$E,2,0),""Unknown""))))"

    End If

Next

 

[Fluff]

Your formula contains a mix of R1C1 & A1 notation, it should be one or the other, but not mixed.