replacing values in vba

  • Thread starter Thread starter maciej.grzywna
  • Start date Start date
M

maciej.grzywna

Hi,
i have a problem with one of my column, i want to get rid of #N/D! and
0 values, but leave the rest. I have already coverted #N/D! to text
format, but following piece of vba doesn't work. Whats wrong with it?

TIA
Maciek

Sub test2()

Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "V").Value = "#N/D!" Then
.Cells(i, "V").Value = ""

ElseIf .Cells(i, "V").Value = "0" Then
.Cells(i, "V").Value = ""
End If
Next i
End With

End Sub
 
Hi

Works fine for me
Are you sure it is column V where you are wanting to make the replacements?
You are carrying out your count on column A, but selecting V for the
replacements.
 
Everything is correct - replacements in column V and counting on
column A. I still get "type mismatch" error :(
 
Is #ND a non-English form of #NA (error code for Not Available)?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Everything is correct - replacements in column V and counting on
column A. I still get "type mismatch" error :(
 
The trick is to work from the bottom up.
Sub ifrowis()
For i = Cells(Rows.count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = "#N/D!" Or Cells(i, "c") = 0 Then MsgBox i
Next i
End Sub

May be better
Sub replaceem()
Columns("c").Replace "#N/D!", ""
Columns("c").Replace 0, ""
End Sub
 
Sub test2()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If Application.WorksheetFunction.IsNA(.Cells(i, "V")) Then
.Cells(i, "V").Value = ""

ElseIf .Cells(i, "V").Value = "0" Then
.Cells(i, "V").Value = ""
End If
Next i
End With

End Sub


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Yes, you are right, it's non-English form for Not Available error
code.
 
One more--this one checks the .text of the cell.

Sub test2()

Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.count, "A").End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, "V").Text = "#N/D!" Then
.Cells(i, "V").Value = ""

ElseIf .Cells(i, "V").Value = "0" Then
.Cells(i, "V").Value = ""
End If
Next i
End With

End Sub

And you sure you wanted "0", not 0? (text vs number)
 
Thank you, it works!

Niek Otten said:
Sub test2()
Dim i As Long
Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If Application.WorksheetFunction.IsNA(.Cells(i, "V")) Then
.Cells(i, "V").Value = ""

ElseIf .Cells(i, "V").Value = "0" Then
.Cells(i, "V").Value = ""
End If
Next i
End With

End Sub


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Yes, you are right, it's non-English form for Not Available error
code.
Click to expand...
 
Not sure if any ideas here would help.
You might need to use "xlCellTypeFormulas" in addition to
"xlCellTypeConstants"
Again, just an idea.

Sub Demo()
Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next

With Cells(2, "V").Resize(lngLastRow - 1)
.SpecialCells(xlCellTypeConstants, xlErrors).ClearContents

.Replace _
What:="0", _
Replacement:=vbNullString, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

End With
End Sub
 
Back
Top