excel cell contents 1

[hopelessliar]

Does anyone know how to tell whether a cell is an inputted value or a formula? The spreadsheet I'm looking at predicts future values which should be replaced with final values by the user once the data is available. I want to be able to check whether the final values have been input.

I'm not sure how clear I've been with my description here! If it needs further clarification then please shout.

Thanks

 

[01Patrik]

Select the cell - if the formula bar (above the column headers) contains the same value as the cell does, then it is an inputted value. If not, the formula is showed in the formula bar.

(If you cannot see any formyla bar, then select that option in the "view" menu)


Is this what you were after?

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[hopelessliar]

sadly not!

I'm looking for a function that will return for example,

"true" if the cell conatins a formula
"false" if it is an input value

 

[byundt]

hopelessliar,
You can accomplish your objective using a user-defined function (written in VBA). Use it with a worksheet formula like:
=HasFormula(A1) returns True if A1 contains a formula, False otherwise

Function HasFormula(cel As Range) As Boolean
HasFormula = cel.HasFormula
End Function

To install a sub or function, ALT + F11 to open the VBA Editor. Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[01Patrik]

Should work fine.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[TonyJollans]

Hi hopelessliar,

This may not help and Brad's answer is probably what you're after, but depending on your setup you might consider an alternative of looking only at cells with formulae. The first sample below just selects all formulae, the second loops through them ..

[blue]    ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23).Select
    MsgBox "Highlighted cells contain formulae. Please enter correct values."
[/blue]

[blue]    For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
        MsgBox "Cell " & cel.Address & " contains a formula."
    Next[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[hopelessliar]

Thanks for all the help guys, Brad's function was exactly what I needed and Tony's thoughts may well prove useful at another time.

Thanks
again.

 

[youngcougar]

To view all of the formulas a spreadsheet has, you can also press the keys <cntrl><~> (tilda) at the same time to see formulas. to change it back press them again.