Find replace using if statement

[blaisjoel]

I have this code I use to find and replace cell values. I added an If statement to make sure one cell value does not get replaced based on the offset cell. It is not working. Need to know what I am doing wrong.

Dim oFindRng As Range
Dim Col1 As String


Col1 = "A"


Columns(Col1).Select


Set oFindRng = Columns(Col1).EntireColumn.Find(What:="65500", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, After:=ActiveCell)


If[U][I][B] oFindRng[/B][/I][/U].Offset(0, 1).Value = "BEARING FEDERAL MOGUL" Then
ActiveCell.Value = "65500"
End If
Do Until oFindRng Is Nothing
    oFindRng.NumberFormat = "@"
    oFindRng.Value = "065500"
    Set oFindRng = Columns(Col1).FindNext(After:=oFindRng)
Loop

I have tried to replace the underlined code oFindRng by Activecell but this did not work neither.
I tried putting the end if after the loop and that did not work neither.

 

[njimack]

See if this is what you're after, although it would be more efficient and much faster to use Autofilter and Specialcells(xlCellTypeVisible)...

Sub x()
Dim oFindRng As Range, c As Range
Dim Col1 As String
Application.ScreenUpdating = False
Col1 = "A"
Set oFindRng = Columns(Col1).EntireColumn.Find(What:="65500", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, After:=ActiveCell)
For Each c In oFindRng
    If c.Offset(0, 1).Value = "BEARING FEDERAL MOGUL" Then c = "65500"
Next c
oFindRng.NumberFormat = "@"

Application.ScreenUpdating = True
End Sub