Fill #N/A and blank cells with zero
 

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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
---------------
Jacob Skaria


"bijan" wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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
---------------
Jacob Skaria


"bijan" wrote:

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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
---------------
Jacob Skaria


"bijan" wrote:

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

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
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"bijan" wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

Fill #N/A and blank cells with zero
 

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



bijan wrote:

Hi
How can I fill #N/A and Blank cells with Zero in specific area(c10:z200) via
vba.
Grateful for all ideas.

--

Dave Peterson