Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Function: cell contents formula or value?
Hi,
It seems like a trivial thing, but I seem to be unable to locate a worksheet function that allows me to determine, if a given cell contents is a formula or a value. Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
You can use a custom function like:
Function IsFormula(rng1 As Range) As Boolean IsFormula = rng1.HasFormula End Function Also, you can see it directly on the screen using CTRL+~ Hope this helps, Miguel. "J.Smith" wrote: Hi, It seems like a trivial thing, but I seem to be unable to locate a worksheet function that allows me to determine, if a given cell contents is a formula or a value. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Function: cell contents formula or value?
J.Smith wrote:
It seems like a trivial thing, but I seem to be unable to locate a worksheet function that allows me to determine, if a given cell contents is a formula or a value. Try the F5* Special route (options are available there) *or click Edit Go to.. , or Press Ctrl+G Press F5 Special Check "Formulas" OK will select all formula cells on the sheet at one go Then we could apply Format Cells Font tab (to taste) Similarly, we could also select the constants: Press F5 Special Check "Constants" OK then apply format to taste, etc ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
Hi, Another option that you may be interested in is colouring cell contents based on what sort of info they contain eg formula, constant, inputs etc. If so, check out Aaron's colour code macro: http://www.xl-logic.com/xl_files/vba/color_code.zip which is listed on his page: http://www.xl-logic.com/pages/vba.html hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=543604 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
Another option that you may be interested in is colouring cell contents based on what sort of info they contain eg formula, constant, inputs etc. If so, check out Aaron's colour code macro: http://www.xl-logic.com/xl_files/vba/color_code.zip which is listed on his page: http://www.xl-logic.com/pages/vba.html hth Rob Brockett NZ Thanks, Rob! This is exactly the purpose I need it for. I want to use a formula which detects if a formula is used in a current cell, in order to apply conditional formatting, so those cells with formula would show up differently (and the user would have a choice to either overwrite them with values, or leave them with calculated defaults). Thank you for your reply! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
You can use a custom function like: Function IsFormula(rng1 As Range) As Boolean IsFormula = rng1.HasFormula End Function Also, you can see it directly on the screen using CTRL+~ Hope this helps, Miguel. Thank you, Miguel! I was hoping, there would be a spreadsheet function, but looks like there is none. If that's the case, then, of course, a VB function is the last resort. Thanks, again, for your reply. |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Function: cell contents formula or value?
Thanks, Max, but I was looking for a worksheet function which does that.
Thanks for replying, anyway! Try the F5* Special route (options are available there) *or click Edit Go to.. , or Press Ctrl+G Press F5 Special Check "Formulas" OK will select all formula cells on the sheet at one go Then we could apply Format Cells Font tab (to taste) Similarly, we could also select the constants: Press F5 Special Check "Constants" OK then apply format to taste, etc ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Function: cell contents formula or value?
J.Smith wrote:
Thanks, Max, but I was looking for a worksheet function which does that. Thanks for replying, anyway! No prob, was just venturing a guess as to your underlying intent. Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
Hi Thanks for the feedback, pleased I could help. I hope it works as you want/expect it to - from memory, it permanently overwrites the other formatting that you have on your sheets. Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=543604 |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Function: cell contents formula or value?
J.Smith wrote...
It seems like a trivial thing, but I seem to be unable to locate a worksheet function that allows me to determine, if a given cell contents is a formula or a value. No built-in function to do this. You could use VBA to write a user-defined function. UDFs need to go into general VBA modules, not class modules. Function isformula(r As Range) As Boolean isformula = r.Cells(1).HasFormula End Function |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
Another option that you may be interested in is colouring cell contents based on what sort of info they contain eg formula, constant, inputs etc. If so, check out Aaron's colour code macro: http://www.xl-logic.com/xl_files/vba/color_code.zip which is listed on his page: http://www.xl-logic.com/pages/vba.html This is exactly the kind of thing I was hoping to accomplish (cell coloring). Thanks a lot! P.S. I posted a reply a long time ago, but it never showed on this NG. Strange! This is a second try. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function: cell contents formula or value?
Thank you, Miguel!
I was hoping for a worksheet function, but looks like there really isn't any. In this case, a VB function should do. Thanks, again! (I posted my thanks awhile ago, but for some reason I can't see it in this NG. Weird!) You can use a custom function like: Function IsFormula(rng1 As Range) As Boolean IsFormula = rng1.HasFormula End Function Also, you can see it directly on the screen using CTRL+~ Hope this helps, Miguel. "J.Smith" wrote: Hi, It seems like a trivial thing, but I seem to be unable to locate a worksheet function that allows me to determine, if a given cell contents is a formula or a value. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function to test formula in a cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |