If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Function to determine if a cell contains a formula
In excel 2007, is there a formula to determine whether or not a cell contains
a formula? Basically I want to do and If/Then function that will return a certain answer if a cell contains a formula and will return a different answer if the cell only contains text (that was manually entered in). Thank you. DoubleZ 
Ads 
#2




Function to determine if a cell contains a formula
Hi,
How about a UDF. Alt + F11 to open VB editor. Right click 'this workbook' and insert module and paste the code in Call with =isformula(A1) Function IsFormula(cel As Range) As String If cel.HasFormula Then IsFormula = "It's a formula" Else IsFormula = "It's not a formula" End If End Function  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "DoubleZ" wrote: > In excel 2007, is there a formula to determine whether or not a cell contains > a formula? Basically I want to do and If/Then function that will return a > certain answer if a cell contains a formula and will return a different > answer if the cell only contains text (that was manually entered in). > > Thank you. > DoubleZ 
#3




Function to determine if a cell contains a formula
Unfortunately, no. You could use a short UDF to determine this though. To
install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the following in: '========== Function IsFormula(r As Range) As Boolean IsFormula = Left(r.Formula, 1) = "=" End Function '========== Close the VBE, and back in your workbook, you can simply use the formula =IsFormula(A1) to check if a cell contains a formula or not.  Best Regards, Luke M "DoubleZ" > wrote in message ... > In excel 2007, is there a formula to determine whether or not a cell > contains > a formula? Basically I want to do and If/Then function that will return a > certain answer if a cell contains a formula and will return a different > answer if the cell only contains text (that was manually entered in). > > Thank you. > DoubleZ 
#4




Function to determine if a cell contains a formula
For many years, the documentation for the CELL function stated that
you could use it to determine whether a cell has a formula, but it never worked. So they changed the documentation. You have to use a VBA function: Public Function HasFormula(R As Range) As Boolean HasFormula = R(1,1).HasFormula End If Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998  2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 25 Mar 2010 10:17:03 0700, DoubleZ > wrote: >In excel 2007, is there a formula to determine whether or not a cell contains >a formula? Basically I want to do and If/Then function that will return a >certain answer if a cell contains a formula and will return a different >answer if the cell only contains text (that was manually entered in). > >Thank you. >DoubleZ 
#5




Function to determine if a cell contains a formula
Borrowing from Mike's post...
Function IsFormula(r As Range) As Boolean IsFormula = r.HasFormula End Function  Best Regards, Luke M "Luke M" > wrote in message ... > Unfortunately, no. You could use a short UDF to determine this though. To > install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the > following in: > > '========== > Function IsFormula(r As Range) As Boolean > IsFormula = Left(r.Formula, 1) = "=" > End Function > '========== > > Close the VBE, and back in your workbook, you can simply use the formula > =IsFormula(A1) > to check if a cell contains a formula or not. > >  > Best Regards, > > Luke M > "DoubleZ" > wrote in message > ... >> In excel 2007, is there a formula to determine whether or not a cell >> contains >> a formula? Basically I want to do and If/Then function that will return >> a >> certain answer if a cell contains a formula and will return a different >> answer if the cell only contains text (that was manually entered in). >> >> Thank you. >> DoubleZ > > 
#6




Function to determine if a cell contains a formula
Thank you Mike and Luke! I am surprised to hear that Excel does not have
that functionality built in, but at least it isn't a difficult UDF. Thanks again. DoubleZ "Luke M" wrote: > Unfortunately, no. You could use a short UDF to determine this though. To > install the UDF, open the VBE (Alt+F11) and goto Insert, module. Paste the > following in: > > '========== > Function IsFormula(r As Range) As Boolean > IsFormula = Left(r.Formula, 1) = "=" > End Function > '========== > > Close the VBE, and back in your workbook, you can simply use the formula > =IsFormula(A1) > to check if a cell contains a formula or not. > >  > Best Regards, > > Luke M > "DoubleZ" > wrote in message > ... > > In excel 2007, is there a formula to determine whether or not a cell > > contains > > a formula? Basically I want to do and If/Then function that will return a > > certain answer if a cell contains a formula and will return a different > > answer if the cell only contains text (that was manually entered in). > > > > Thank you. > > DoubleZ > > > . > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Function to determine whether cell contains a formula?  Don  Excel Worksheet Functions  4  March 2nd 07 05:11 AM 
Value of cell to determine range in MAX Function  [email protected]  Excel Worksheet Functions  8  February 18th 06 07:34 PM 
Is there a way to determine which cell the function =min() used?  Justin  Excel Worksheet Functions  6  October 16th 05 01:14 PM 
Function to determine if any cell in a range is contained in a given cell  [email protected]  Excel Worksheet Functions  3  February 7th 05 05:19 PM 
Is there a function to determine whether a cell contains a formul.  Christo Kriel  Excel Worksheet Functions  1  November 20th 04 07:54 PM 