VBA HELP - Replace (Not Equal)

[wakerider017]

I have the below code which seems to work well at replacing all in D7:D502 with #N/A that matches G1...

BUT, I want to replace all in D7:D502 with #N/A that does NOT match G1. How do I do that??

Range("D7:D502").Replace Range("G1"), "#N/A", xlWhole

 

[Dim Me as xlNoob]

I'm not well versed in the replace function so I don't know if there is a way to use it how you want.

However you can loop through your range with an if statement:

Dim cell As Range

For Each cell In Range("D7:D502")
     If cell.Value <> Range("G1").value Then cell.Value = "#N/A"
Next cell

 

[Scott Huish]

Without a loop, perhaps:

Sub test2()
  Dim Addr As String
  Addr = "D7:D502"
  Range(Addr) = Evaluate(Replace("IF(@<>G1,""#N/A"",g1)", "@", Addr))
End Sub

 

[wakerider017]

Sorry for the late reply.

I ended up using the code found here: https://www.thespreadsheetguru.com/...ells-not-containing-a-specific-word-or-phrase

[COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] DoNotContainClearCells()[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'PURPOSE: Clear out all cells that do not contain a specific word/phrase[/FONT][/COLOR]
[COLOR=#007F00][FONT='inherit']'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] rng [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] Range[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] cell [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] Range[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Dim[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] ContainWord [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']As[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']String[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'What range do you want to search?[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Set[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] rng = Range("A1:A10")[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'What phrase do you want to test for?[/FONT][/COLOR]
[COLOR=#3D3D3D][FONT='inherit']  ContainWord = "Magic"[/FONT][/COLOR]

[COLOR=#007F00][FONT='inherit']'Loop through each cell in range and test cell contents[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']For[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Each[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] cell [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']In[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] rng.Cells[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']If[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] cell.Find(ContainWord) [/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Is[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Nothing[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Then[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] cell.Clear[/FONT][/COLOR]
[COLOR=#00007F][FONT='inherit']Next[/FONT][/COLOR][COLOR=#3D3D3D][FONT='inherit'] cell[/FONT][/COLOR]

[COLOR=#00007F][FONT='inherit']End[/FONT][/COLOR][COLOR=#00007F][FONT='inherit']Sub[/FONT][/COLOR]

And replaced cell.Clear with cell.Value = "#N/A"