Searching for empty cells and changing values

[PosseJohn]

I have a worksheet that contains sample results for chemical analysis.

Not all analytes are analyzed each time, thus some of the cells are empty.

To make the graphs more usable, I want to search the data range for empty
cells and place =NA() in them so that the trend lines and continuous.

Here's what I have been trying...
For Each cl In Worksheets("Data").Range("A2:AC" & LastRowUsed).Cells
If cl.Value = "" Then
cl.Value = "=NA()"
End If
Next

Each time I have tried this, the code errors out on me when it reaches a
cell that already has the value set to =NA().

Is there a better approach to what I'm attempting, perhaps a way to set a
command to do the entire range at once, instead of evaluating each cell?

Thanks in advance, and HAPPY HALLOWEEN.

 

[Don Guillett]

Try

ON ERROR RESUME NEXT
For Each cl In Worksheets("Data").Range("A2:AC" & LastRowUsed).Cells
If cl.Value = "" Then
cl.Value = "=NA()"
End If
Next

 

[Mike H]

Hi,

Test for an error

For Each cl In Worksheets("Data").Range("A2:AC" & 5).Cells
If Not IsError(cl.Value) Then
If cl.Value = "" Then
cl.Value = "=NA()"
End If
End If
Next

Mike

 

[Mike H]

OOPS I changed you variable LastRowUsed to a number to save me from
populating it so change it back

 

[PosseJohn]

Thanks Mike.

OOPS I changed you variable LastRowUsed to a number to save me from
populating it so change it back

 

[Mike H]

your welcome

Thanks Mike.

 

[Rick Rothstein]

I think you could do this without a loop using a single statement...

Worksheets("Data").Range("A2:AC" & LastRowUsed). _
SpecialCells(xlCellTypeBlanks).Formula="=NA()"