VBA - Remove N/A

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

1. I want to write VBA code for - check if a cell has
value - "N/A" and change the value of that cell to 0.

2. Check if the cell is empty and if it is go to the next
row.

Thanks in advance.
 
dipsy said:
1. I want to write VBA code for - check if a cell has
value - "N/A" and change the value of that cell to 0.
Click to expand...

If ActiveCell.Value = CVErr(xlErrNA) Then
ActiveCell.Value = 0
End If
2. Check if the cell is empty and if it is go to the next
row.
Click to expand...

If IsEmpty(Activecell.Value) Then
Activecell.Offset(1,0).Select
End If

although it is rarely necessary to select
 
This error is the result off a formula
If you make it of the formula is gone
Do you want that???


Try this if you want to check the activecell

If IsEmpty(ActiveCell.Value) Then
ActiveCell.Offset(1, 0).Select
End If
 
Sub errortozero()
x = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1)
ActiveCell.Formula = "=if(iserror(" & x & "),0," & x & ")"
End Sub

This is the code for n/a to zero

Claire
 
You know, you don't need VBA to get this result. You can use the ISERROR
function within an if statement and achieve the same thing.
--
RMC,CPA


1. I want to write VBA code for - check if a cell has
value - "N/A" and change the value of that cell to 0.

2. Check if the cell is empty and if it is go to the next
row.

Thanks in advance.
 
Not sure how many you're doing, but you may want to select your range and then
edit|replace to clear those cells. And if you need a macro, you can record one
when you do it the first time.
 
Back
Top