Yet another VBA issue... Find and Replace

[flicky_longcartridge]

Hi Guys,

I am trying to execute a simple find and replace statement...

It needs to loops through all the cells in column H of the sheet called HIST. If it finds the value #N/A then it needs to replace it with the value 0.00

I have tried...

Sub findrep()
Dim target, cell As Range
Dim i, k As String
i = "#N/A"
k = "0.00"
Set target = Sheets("HIST").Range(Range("D1"), Range("D65536").End(xlUp))
For Each cell In target
If cell.Value = i Then cell.Value = k
Next cell
End Sub

But with no success...

Help experts please!!!!

 

[ktab]

Sub findrep()
    Sheets("HIST").Columns(4).Replace What:="#N/A", Replacement:="0.00"
End Sub

try it simple way.. The "#N/A" is a result of a formula?

 

[texasalynn]

if the #N/A is the result of a formula then it will not replace. If you just don't want the #N/A to display you can do something with the formula

=if(isna(vlookup(a1,table,3,0),0,vlookup(a1,table,3,0))
this is just an example

 

[flicky_longcartridge]

Thats excellent.

The #N/A was a result of a vLookup not finding a value but i pasted just the value back into the cell.

Sub findrep()
Sheets("HIST").Columns(4).Replace What:="#N/A", Replacement:="0.00"
End Sub

Worked nicely..

Thanks for your help!!

 

[ktab]

So if you want to copy-paste also (values only) at column 4 (D:D) use this code:

Sub findrep()
    With Sheets("HIST").Columns(4)
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Replace What:="#N/A", Replacement:="0.00"
    End With
End Sub

This code, first copy all column 4 back at it's place as values only, and then replaces.