Fill #N/A and blank cells with zero

  • Thread starter Thread starter bijan
  • Start date Start date
B

bijan

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.
 
Try the below macro....You can deal this within your fomulas itself; like
=IF(ISNA(formula),0,formula)


Sub Macro()
For Each cell In Range("C10:z200")
If cell.Text = "" Or cell.Text = "#N/A" Then cell.Value = 0
Next
End Sub

If this post helps click Yes
 
Thanks for answer,But may I ask you another question,
after this step I need to save as or copy this sheet just from(c10:z200) in
text format to specific path(E:\TEST\text.txt) any ideas would be appreciated.
Bijan
 
Try the below

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Dim rngTemp As Range

Set rngTemp = ActiveSheet.Range("C10:Z100")
For Each cell In rngTemp
If cell.Text = "" Or cell.Text = "#N/A" Then cell.Value = 0
Next

Set wb = Workbooks.Add
Set ws = ActiveSheet
rngTemp.Copy ws.Range("A1")
Application.DisplayAlerts = False
wb.SaveAs Filename:="C:\testtxt1.txt", FileFormat:=xlText, _
CreateBackup:=False
wb.Close
Application.DisplayAlerts = True
End Sub


If this post helps click Yes
 
Thank you,For last Question
If my cell range is unknown(C10:Z) and the sheet is non-actived with
name(TEST) then how can I change this code?
Bijan
 
Try

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Dim rngTemp As Range

Set rngTemp = Sheets("test").Usedrange
For Each cell In rngTemp
If cell.Text = "" Or cell.Text = "#N/A" Then cell.Value = 0
Next

Set wb = Workbooks.Add
Set ws = ActiveSheet
rngTemp.Copy ws.Range("A1")
Application.DisplayAlerts = False
wb.SaveAs Filename:="C:\testtxt1.txt", FileFormat:=xlText, _
CreateBackup:=False
wb.Close
Application.DisplayAlerts = True
End Sub


If this post helps click Yes
 
Another option is to record a macro when you

select the range
edit|goto|special|Formulas|Uncheck numbers, text, logicals, but leave errors
checked.
click ok
type 0 and hit ctrl enter to fill those cells.

Repeat the process for Constants
and
repeat it once more for blanks
 
Back
Top