Validate data part 2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ricoy-Chicago - 13 Oct 2006 19:11 GMT

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.

Reply to this Message

Dave Peterson - 13 Oct 2006 19:40 GMT

dim myCell as range
dim myRng as range
set myrng = worksheets("sheet999").range("a1:D9")

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("a1:D9")
on error resume next
myrng.cells.specialcells(xlcelltypeblanks).value = 0
on error goto 0

> The coding given by Dave works fine. it traps erros as a whoel in the range.
> However...
[quoted text clipped - 13 lines]
>
> Thanx again.

Signature

Dave Peterson

Reply to this Message

Ricoy-Chicago - 13 Oct 2006 19:51 GMT

You are the Man. Thanx

> dim myCell as range
> dim myRng as range
[quoted text clipped - 31 lines]
> >
> > Thanx again.

Reply to this Message

Rate this thread: