Cell Formula vs Cell Value
 

I have a procedure that performs the following. When a user puts a 'Y' in a
certain cell (say B5), then another cell (say A5) is populated with a
formula. When an 'N' is placed in B5, the formula in A5 is removed.
A user is allowed to put his own value in cell A5, but I would like cell B5
to change to N if cell A5 contains a number and not a formula. I have the
following, but need help:

If (tCell.Formula) = "" And tCell.Offset(0, iColumnOffset).Value < "N" Then
tCell.Offset(0, iColumnOffset).Value = "N"

This only works when the cell A5 is clear. Any suggestions?

Cell Formula vs Cell Value
 

that is because a number in a cell is considered a formula so to speak

ActiveCell.Value = 5
? activecell.Formula
5

But the HasFormula property only pays attention to a true formula.

? activecell.HasFormula
False

so change to

If Not tCell.HasFormula And tCell.Offset(0, iColumnOffset).Value < "N"
Then
tCell.Offset(0, iColumnOffset).Value = "N"

--
Regards,
Tom Ogilvy


"Matt" wrote in message
...
I have a procedure that performs the following. When a user puts a 'Y' in
a
certain cell (say B5), then another cell (say A5) is populated with a
formula. When an 'N' is placed in B5, the formula in A5 is removed.
A user is allowed to put his own value in cell A5, but I would like cell
B5
to change to N if cell A5 contains a number and not a formula. I have the
following, but need help:

If (tCell.Formula) = "" And tCell.Offset(0, iColumnOffset).Value < "N"
Then
tCell.Offset(0, iColumnOffset).Value = "N"

This only works when the cell A5 is clear. Any suggestions?

Cell Formula vs Cell Value
 

awesome. Thanks.

"Tom Ogilvy" wrote:

that is because a number in a cell is considered a formula so to speak

ActiveCell.Value = 5
? activecell.Formula
5

But the HasFormula property only pays attention to a true formula.

? activecell.HasFormula
False

so change to

If Not tCell.HasFormula And tCell.Offset(0, iColumnOffset).Value < "N"
Then
tCell.Offset(0, iColumnOffset).Value = "N"

--
Regards,
Tom Ogilvy


"Matt" wrote in message
...
I have a procedure that performs the following. When a user puts a 'Y' in
a
certain cell (say B5), then another cell (say A5) is populated with a
formula. When an 'N' is placed in B5, the formula in A5 is removed.
A user is allowed to put his own value in cell A5, but I would like cell
B5
to change to N if cell A5 contains a number and not a formula. I have the
following, but need help:

If (tCell.Formula) = "" And tCell.Offset(0, iColumnOffset).Value < "N"
Then
tCell.Offset(0, iColumnOffset).Value = "N"

This only works when the cell A5 is clear. Any suggestions?