Range.Replace IF another cell value is:

Question

I am trying to use the find and replace function based on another cell value (in another column).

For example:

I want to do this change:

Columns("OEM").Replace What:="Aerostar Aircraft Corporation", _
    Replacement:="Aerostar Aircraft Corp", _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    MatchCase:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False

only If the value of the column "Model" (for that same row) is "DW100".

How can that code be written?

Columns("OEM").Replace What:="Aerostar Aircraft Corporation", _
    Replacement:="Aerostar Aircraft Corp", _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    MatchCase:=False, _
    SearchFormat:=False, _
    ReplaceFormat:=False

Answer 1

Please try.

• OEM and Medel are named range (Column A and C).

Option Explicit
Sub Demo()
    Dim RngModel As Range
    Set RngModel = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("Model"))
    RngModel.AutoFilter
    RngModel.AutoFilter Field:=1, Criteria1:="DW100"
    Dim RngOEM As Range
    Set RngOEM = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("OEM"))
    RngOEM.Replace What:="Aerostar Aircraft Corporation", _
        Replacement:="Aerostar Aircraft Corp", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    RngModel.AutoFilter
End Sub