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
|
|||
|
|||
Formula or not?
Hi,
Is there a formula to establish whether a cell contains a value or a formula? What I mean is say A1 contained =10+20 so it shows 30 and it contains a formula. If I then paste special values it still shows 30 but there is no formula as there would not be in an empty cell. What I'm looking for is a formula in another cell which will give a TRUE or FALSE if there is a formula in A1 or not. TIA, Regards, Alan. |
#2
|
|||
|
|||
Hi Alan,
A simple User define Function (UDF) will do. Function CellHasFormula(a As Range) As Boolean CellHasFormula = a.HasFormula End Function Open the VB editor (ALT+F11) From the menu: InsertModule Paste the above function in the module Return to the worksheet. You can now use CellHasFormula(A1) as if it were a built-in function. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Alan" wrote in message ... Hi, Is there a formula to establish whether a cell contains a value or a formula? What I mean is say A1 contained =10+20 so it shows 30 and it contains a formula. If I then paste special values it still shows 30 but there is no formula as there would not be in an empty cell. What I'm looking for is a formula in another cell which will give a TRUE or FALSE if there is a formula in A1 or not. TIA, Regards, Alan. |
#3
|
|||
|
|||
Hi Niek,
That is perfect, Thank You! Once again my thanks to you and the experts in these newsgroups, Regards, Alan. "Niek Otten" wrote in message ... Hi Alan, A simple User define Function (UDF) will do. Function CellHasFormula(a As Range) As Boolean CellHasFormula = a.HasFormula End Function Open the VB editor (ALT+F11) From the menu: InsertModule Paste the above function in the module Return to the worksheet. You can now use CellHasFormula(A1) as if it were a built-in function. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Alan" wrote in message ... Hi, Is there a formula to establish whether a cell contains a value or a formula? What I mean is say A1 contained =10+20 so it shows 30 and it contains a formula. If I then paste special values it still shows 30 but there is no formula as there would not be in an empty cell. What I'm looking for is a formula in another cell which will give a TRUE or FALSE if there is a formula in A1 or not. TIA, Regards, Alan. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |
Excel Formula | yuki | Worksheet Functions | 7 | September 28th, 2004 02:40 AM |
Add to formula | Pat | Worksheet Functions | 16 | September 24th, 2004 03:38 PM |
Alternative formula to HLookup | Bernie Deitrick | Worksheet Functions | 0 | March 26th, 2004 12:53 PM |