Formula VS Data Entry

[Trevor Gartner]

I think I may stump most of you on this.

Can anyone tell me how what mehtod to use to determine if
a cell has a formula typed in it or if it has data in it?

eg:
=sum(a12:b12) vs 788

something life = if(isformula(c),"Formula",Data")

Althought there are VBA methods to achieve this, they tne
do be memmory pigs and can bog down your app. I need a
low memory solution as I need to use this in many cells.

Thanks
Trevor Gartner

 

[Leo Heuser]

Trevor

It's no big deal in VBA, if you use the
HasFormula property.

The worksheet function TYPE is supposed
to deliever the information, e.g.
=IF TYPE(C1)=8,"Formula"
but to the best of my knowledge nobody has
been able to get get the return value 8, if
the cell contains a formula.

 

[Alan]

A (crude) formula solution,
As a formula has to begin with '=' which is CHAR(61) in "Excel ASCII",
=IF(LEFT(A1,1)=CHAR(61),"Formula","Data")
Regards,
Alan.

 

[Peo Sjoblom]

Won't work, left(whatever will not return the equal sign but whatever
the formula returns so using the OPs example the left part returns "7"
regardless if it is =sum(a12:b12) = 788 or just the 788

 

[Harald Staff]

A (crude) formula solution,
As a formula has to begin with '=' which is CHAR(61) in "Excel ASCII",
=IF(LEFT(A1,1)=CHAR(61),"Formula","Data")

Can you make this display "Formula", if so how ? Does not work here. Too bad.

Best wishes Harald
Followup to newsgroup only please.

 

[Trevor Gartner]

Leo, thanks for the info. I tried this TYPE function, but
same results. I have this, which dooes work but has a
memory issue:

Function CellType(c)
Application.Volatile
Set c = c.Range("A1")
If c.HasFormula Then CellType = "True" Else CellType
= "False"
End Function

 

[Leo Heuser]

You're welcome Trevor.
Using Application.Volatile makes Excel
recalculate the function for *each* cell calling
it, *every* time some kind of recalculation takes
place, and it takes time.
If you remove the line from the function,
it will only recalc, when the argument to the
function changes in a cell, and then only for
that cell. Is it necessary to include the line
here?
A shorter version of your function would be:

Function CellType(c As Range) As Boolean
Application.Volatile
CellType = (c.HasFormula)
End Function

or

Function CellType(c As Range) As Boolean
CellType = (c.HasFormula)
End Function

and from the worksheet: =celltype(b4)

 

[Dale Hymel]

Try This macro

Sub Macro1()
Selection.SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = 6
Range("A1").Select
End Sub


You can also put in in one of the worksheet event (activate,change, etc) so
that added cells are automated colored when a formula is entered.

 

[Alan]

No,
Sorry,
Brain in gear etc,
Alan.

Can you make this display "Formula", if so how ? Does not work here. Too bad.

Best wishes Harald
Followup to newsgroup only please.