Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|