C.Value #N/A
 

I have code in my spreadshhet that goes through a range of cells and if they
are not blank it holds the value and not the formula:

If c.value < "" then c.value = c.value
Next

This works perfectly however I have changed my formula in the spreadsheet to
show #N/A instead of "". How do I convert this in the code to say - "if the
cell value is not #N/A then c.value = c.value"?

C.Value #N/A
 

If Worksheetfunction.isna(c.value) = False Then

End If

If this post helps click Yes
---------------
Jacob Skaria


"Ryan" wrote:

I have code in my spreadshhet that goes through a range of cells and if they
are not blank it holds the value and not the formula:

If c.value < "" then c.value = c.value
Next

This works perfectly however I have changed my formula in the spreadsheet to
show #N/A instead of "". How do I convert this in the code to say - "if the
cell value is not #N/A then c.value = c.value"?

C.Value #N/A
 

You could use iserror() to check for any error or use c.text to compare what
shows up in the cell.

if iserror(c.value) then
'skip it

or
if c.text = "#N/A" then
'skip it

======
But you may find that your code works faster (maybe lots if the number of cells
is large) by copy|pasting values over that range

dim myRng as Range
with activesheet
set myrng = .range("a1:c9999")
end with
myrng.copy
myrng.pastespecial paste:=xlpastevalues

or even just assign the value:

dim myRng as Range
with activesheet
set myrng = .range("a1:c9999")
end with
with myrng
.value = .value
end with

=====
If your range is non-contiguous, you'll want to loop through each area:

dim myArea as range
dim myRng as range
with activesheet
set myrng = .range("a1:c9999,e3:f9")
end with
for each myarea in myrng.areas
with myarea
.value = .value
end with
next myarea




Ryan wrote:

I have code in my spreadshhet that goes through a range of cells and if they
are not blank it holds the value and not the formula:

If c.value < "" then c.value = c.value
Next

This works perfectly however I have changed my formula in the spreadsheet to
show #N/A instead of "". How do I convert this in the code to say - "if the
cell value is not #N/A then c.value = c.value"?

--

Dave Peterson