Searching for empty cells and changing values
 

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.

Searching for empty cells and changing values
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"PosseJohn" wrote in message
...
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.

Searching for empty cells and changing values
 

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

"PosseJohn" wrote:

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.

Searching for empty cells and changing values
 

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

"Mike H" wrote:

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

"PosseJohn" wrote:

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.

Searching for empty cells and changing values
 

Thanks Mike.

"Mike H" wrote:

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

"Mike H" wrote:

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

"PosseJohn" wrote:

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.

Searching for empty cells and changing values
 

your welcome
"PosseJohn" wrote:

Thanks Mike.

"Mike H" wrote:

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

"Mike H" wrote:

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

"PosseJohn" wrote:

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.

Searching for empty cells and changing values
 

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

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
OOPS I changed you variable LastRowUsed to a number to save me from
populating it so change it back

"Mike H" wrote:

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

"PosseJohn" wrote:

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.