C.Value #N/A

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

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"?
 
If Worksheetfunction.isna(c.value) = False Then

End If

If this post helps click Yes
 
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
 
Back
Top