Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ruthki
 
Posts: n/a
Default how can i test whether cell contains keyed constant or a formulae


I would like to use conditional formatting to test whether a cell
contains text or a formulae and higlight all cells as they are
overtyped.

I have tried using Type() but doesnt recognise difference between sam a
number keyed in and a formulae beginning =if(. . . .

Any ideas?


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=380978

  #2   Report Post  
anilsolipuram
 
Posts: n/a
Default


You have to write a userdefined function which can determine whether a
cell has formula or not

below is that funtion which determines whether a cell has formula or
not, it takes the cell address as the parameter

Function check_formula(r As Range)
If r.Formula = "" Then
check_formula = False
Else
check_formula = True
End If

End Function

You have to paste the the above function in vba module.

to use it in any cell type , =check_formula(b3) ' this will find
whether cell b3 has formula or not , if formula is there it will return
true, else false


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380978

  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

I bet you meant something more like:

Function check_formula(r As Range)
If r.hasFormula Then
check_formula = true
Else
check_formula = false
End If

End Function

anilsolipuram wrote:

You have to write a userdefined function which can determine whether a
cell has formula or not

below is that funtion which determines whether a cell has formula or
not, it takes the cell address as the parameter

Function check_formula(r As Range)
If r.Formula = "" Then
check_formula = False
Else
check_formula = True
End If

End Function

You have to paste the the above function in vba module.

to use it in any cell type , =check_formula(b3) ' this will find
whether cell b3 has formula or not , if formula is there it will return
true, else false

--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380978


--

Dave Peterson
  #4   Report Post  
Ruthki
 
Posts: n/a
Default


Thanks Anil - It worked and I learnt a bit more.


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=380978

  #5   Report Post  
Ruthki
 
Posts: n/a
Default


Dave
Will this differentiate between a keyed constant and a formula - the
difficulty I have encountered is that the keyed constant is recognised
as a formula and therefore result will be true for both ?


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=380978



  #6   Report Post  
kk
 
Posts: n/a
Default

Try http://www.j-walk.com/ss/excel/usertips/tip045.htm . It works for me.


"Ruthki" wrote in
message ...

I would like to use conditional formatting to test whether a cell
contains text or a formulae and higlight all cells as they are
overtyped.

I have tried using Type() but doesnt recognise difference between sam a
number keyed in and a formulae beginning =if(. . . .

Any ideas?


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile:
http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=380978



  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

r.hasFormula

will be true if the cell r has a formula. If it has text that starts with an
equal sign (but still text), it will return false.



Ruthki wrote:

Dave
Will this differentiate between a keyed constant and a formula - the
difficulty I have encountered is that the keyed constant is recognised
as a formula and therefore result will be true for both ?

--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=380978


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Test a cell for a formula present Trebor Retrac Excel Discussion (Misc queries) 1 May 30th 05 05:11 PM
Function syntax to compare cell contents ES Excel Worksheet Functions 2 May 18th 05 03:53 PM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 08:18 AM
VLookup resulting in a blank cell... KempensBoerke Excel Worksheet Functions 1 October 28th 04 09:57 PM


All times are GMT +1. The time now is 07:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"