Validate data part 2

[Guest]

The coding given by Dave works fine. it traps erros as a whoel in the range.
However...

I have a problem with null values, cells in range can have null values, null
values are OK. I want so search each cell in the range then if its contents
is null replace it with zero. Something like:

define range

for each cell in range
if value is null then
value = 0
end if

I do not know how to loop through the cells in the range.

Thanx again.

 

[Dave Peterson]

dim myCell as range
dim myRng as range
set myrng = worksheets("sheet999").range("a19")

for each mycell in myrng.cells
if isempty(mycell.value) then
mycell.value = 0
end if
next mycell

or to get them all at once...

dim myRng as range
set myrng = worksheets("sheet999").range("a19")
on error resume next
myrng.cells.specialcells(xlcelltypeblanks).value = 0

 

[Guest]

You are the Man. Thanx

dim myCell as range
dim myRng as range
set myrng = worksheets("sheet999").range("a19")

for each mycell in myrng.cells
if isempty(mycell.value) then
mycell.value = 0
end if
next mycell

or to get them all at once...

dim myRng as range
set myrng = worksheets("sheet999").range("a19")
on error resume next
myrng.cells.specialcells(xlcelltypeblanks).value = 0