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
|
