I want to populate specific column values with a zero if
the value in another specified column is not null.

As an example, I would like to place a 0 in columns C & E
of my spreadsheet if the value in column A is null.

I presume that I would have to start by specifying the
range which I would like this macro to work over. If that
is so, is it possible for the range to set itself by using
any row that has data on it (bar row1) ? This way, the
range can vary depending on how many rows of data are in
the spreadsheet at any given time.

Thanks in advance.

Try the following (untested!)

Dim cell as range
for each cell in intersect(columns(1), activesheet.usedrange)
if not isempty(cell) then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Steve" wrote in message
...
I want to populate specific column values with a zero if
the value in another specified column is not null.

As an example, I would like to place a 0 in columns C & E
of my spreadsheet if the value in column A is null.

I presume that I would have to start by specifying the
range which I would like this macro to work over. If that
is so, is it possible for the range to set itself by using
any row that has data on it (bar row1) ? This way, the
range can vary depending on how many rows of data are in
the spreadsheet at any given time.

Thanks in advance.

Bob, this appears to put zeros in every row of columns 3
and 5 regardless of whether column 1 is noull or not.

Steve
-----Original Message-----
Try the following (untested!)

Dim cell as range
for each cell in intersect(columns(1),
activesheet.usedrange)
if not isempty(cell) then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel

"Steve" wrote in
message
...
I want to populate specific column values with a zero if
the value in another specified column is not null.

As an example, I would like to place a 0 in columns C &
E
of my spreadsheet if the value in column A is null.

I presume that I would have to start by specifying the
range which I would like this macro to work over. If
that
is so, is it possible for the range to set itself by
using
any row that has data on it (bar row1) ? This way, the
range can vary depending on how many rows of data are in
the spreadsheet at any given time.

Thanks in advance.


.

Are column A cells actually empty or do they all contain a conditional
formula that makes the cell appear emtpy under certain conditions?

Anyway, Bob's code had a reversed condition as I read your description

Dim cell as range
for each cell in intersect(columns(1), _
activesheet.usedrange)
if cell.Value = "" then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next


--
Regards,
Tom Ogilvy


"Steve" wrote in message
...
Bob, this appears to put zeros in every row of columns 3
and 5 regardless of whether column 1 is noull or not.

Steve
-----Original Message-----
Try the following (untested!)

Dim cell as range
for each cell in intersect(columns(1),
activesheet.usedrange)
if not isempty(cell) then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel

"Steve" wrote in
message
...
I want to populate specific column values with a zero if
the value in another specified column is not null.

As an example, I would like to place a 0 in columns C &
E
of my spreadsheet if the value in column A is null.

I presume that I would have to start by specifying the
range which I would like this macro to work over. If
that
is so, is it possible for the range to set itself by
using
any row that has data on it (bar row1) ? This way, the
range can vary depending on how many rows of data are in
the spreadsheet at any given time.

Thanks in advance.


.

Tom

Column A is actually empty (there is no formula
involved). I have tried your amended version of Bob's
solution and I have success.

Many thanks
Steve
-----Original Message-----
Are column A cells actually empty or do they all contain
a conditional
formula that makes the cell appear emtpy under certain
conditions?

Anyway, Bob's code had a reversed condition as I read
your description

Dim cell as range
for each cell in intersect(columns(1), _
activesheet.usedrange)
if cell.Value = "" then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next


--
Regards,
Tom Ogilvy


"Steve" wrote in
message
...
Bob, this appears to put zeros in every row of columns 3
and 5 regardless of whether column 1 is noull or not.

Steve
-----Original Message-----
Try the following (untested!)

Dim cell as range
for each cell in intersect(columns(1),
activesheet.usedrange)
if not isempty(cell) then
cells(cell.row,3).value = 0
cells(cell.row, 5).value = 0
end if
next

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB
macros
for Excel

"Steve" wrote in
message
...
I want to populate specific column values with a
zero if
the value in another specified column is not null.

As an example, I would like to place a 0 in columns
C &
E
of my spreadsheet if the value in column A is null.

I presume that I would have to start by specifying
the
range which I would like this macro to work over. If
that
is so, is it possible for the range to set itself by
using
any row that has data on it (bar row1) ? This way,
the
range can vary depending on how many rows of data
are in
the spreadsheet at any given time.

Thanks in advance.


.



.