Cell Formula vs Cell Value

[Guest]

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?

 

[Tom Ogilvy]

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"

 

[Guest]

awesome. Thanks.

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"