Find Replace Cell Value Dynamic Range

[The Chad]

Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have been working on a macro to find either “0” or “#N/A” and replace with “”. The 0 or #N/A is the result of a vlookup where I have done a paste special to retain the value.
<o> </o>
Code that is working (modified from unknown person)

[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Sub FindReplaceABCD()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SFind1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SFind2<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SReplace<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SFind1 = "0"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SFind2 = "#N/A"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SReplace = ""<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Times New Roman]    Range("J2:BC1150").Replace _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        What:=SFind1, Replacement:=SReplace, _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    LookAt:=xlWhole, MatchCase:=False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Range("J2:BC1150").Replace _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        What:=SFind2, Replacement:=SReplace, _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    LookAt:=xlWhole, MatchCase:=False<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

<o> </o>
Code that I am working on where I am getting a Run-time error ‘13’ at the If Statement.

[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Sub FindReplaceabc()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim i, j, lRow, lColumn<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SFind1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SFind2<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim SReplace<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SFind1 = "0"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SFind2 = "#N/A"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SReplace = ""<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    'Application.Calculation = xlCalculationManual<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Application.ScreenUpdating = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    lRow = Range("A" & Rows.Count).End(xlUp).Row<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    lColumn = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    SearchOrder:=xlByColumns).Column<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    For j = 10 To lColumn 'column J (10) to last column<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        For i = lRow To 2 Step -1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'Error occurring in if statement below "Runtime error 13"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            If Cells(i, j).Value = SFind1 Or SFind2 Then<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                Cells(i, j).Value = SReplace<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            End If<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Next i<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Next j<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Application.ScreenUpdating = True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    'Application.Calculation = xlCalculationAutomatic<o:p></o:p>[/FONT][/SIZE]
<o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]

<o> </o>
I am using Excel 2003 and need the find replace in a macro as it is part of a larger piece of work.
<o> </o>
Any assistance is appreciated.

cheers,
Chad

 

[Rick Rothstein]

'Error occurring in if statement below "Runtime error 13"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If Cells(i, j).Value = SFind1 Or SFind2 Then[/Code]

That syntax is not correct for an Or test... you must do two separate comparisons inside the If..Then statement. Try it like this...

If Cells(i, j).Value = SFind1 Or Cells(i, j).Value = SFind2 Then

 

[GTO]

...answered.

 

[The Chad]

sensational - thanks Rick

I also forgot to mention in my original post that I received assistance from Weaver with the if statement for locating each cell.