VBA - Remove N/A

[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.

 

[Bob Phillips]

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.

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.

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

although it is rarely necessary to select

 

[Ron de Bruin]

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

 

[Claire]

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

 

[R. Choate]

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.

 

[Dave Peterson]

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.