Formula VS Data Entry

  • Thread starter Thread starter Trevor Gartner
  • Start date Start date
T

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
 
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.
 
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.
 
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
 
Alan said:
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")
Click to expand...

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

Best wishes Harald
Followup to newsgroup only please.
 
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
 
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)
 
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.
 
No,
Sorry,
Brain in gear etc,
Alan.
Harald Staff said:
Can you make this display "Formula", if so how ? Does not work here. Too bad.

Best wishes Harald
Followup to newsgroup only please.
Click to expand...
 
Back
Top