PDA

View Full Version : [SOLVED] Examining the Formula in a Cell



Cyberdude
04-19-2005, 09:59 AM
I assume that there is no way using formulas to examine the contents of a formula (not the value) in a cell. Correct?
For example, the formula to be examined has a list of items that are AND'ed [AND(A1=2, A2>3,..., A10<12)], and I would like to be able to check to see if an item A6 is in that list. Can that be done with another formula somehow??

Zack Barresse
04-19-2005, 10:39 AM
Can you explain a little more? Possibly give an example or two?

Cyberdude
04-19-2005, 02:13 PM
It's a little hard to explain.

For example, the formula to be examined has a list of items that are AND'ed [AND(A1=2, A2>3,..., A10<12)], and I would like to be able to check to see if an item A6 is in that list.
I have a lot of workbooks that have a few nearly identical formulas that contain lists or sequences as I showed in the quote. For reasons I can't explain, sometimes the list for a particular workbook should have a cell reference in its list, but somehow along the way it got deleted or omitted. I would like to have a formula (ideally) that can make a continuing check of such lists to make sure they are correctly populated. If not, then activates an error message. I have used macros a few times to do this job, but they aren't running continuously. The ones I'm using execute from an event macro. I would just rather use a formula, but I very much doubt if it can be done. This was a stupid question. Don't soend any time on it. Thanks for the response. :hi:

Zack Barresse
04-19-2005, 03:50 PM
I assume that there is no way using formulas to examine the contents of a formula (not the value) in a cell. Correct? ..

Correct.

What I would do is use 2 formulas (possibly coupled together if space is an issue). The first would be a UDF, something like this ...


Function FTEXT(f As Range)
FTEXT = f.Text
If f.HasFormula Then FTEXT = f.Formula
End Function

Which is derived from here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

With this UDF, you can then use the standard FIND function, something like this (assuming the FTEXT() function was used in, say, B1) ..


=FIND("A6",B1,1)

or, if you wanted to use error handling in your formula ...


=IF(ISERR(FIND("A6",B1,1)),"Nope","Yup")

Or you could incorporate all 3 into one ...



=IF(ISERR(FIND("A6",FTEXT(A1),1)),"Nope","Yup")


Is this what you mean Cyberdude? Hope I have understood you correctly. (And there's no such thing as a stupid question!!! :yes )