PDA

View Full Version : Solved: Identify column or number



dalea
05-06-2009, 02:26 PM
I've attaced my workbook. My problem is this. In the area of g8:O12 on the "Cellualr FY2009" worksheet I've entered actual data reported. In the area of P8:R12 there are formulae to estimate future amounts based upon historical data on the other worksheet. What I need to do is either determine how many cells on each line (8-12) have a formula (i.e., begins with a "=") or in the alternative, in which column for each line is the first formula. I would like a formulaic response rather than a macro solution. I could settle for UDF. I've been :banghead: on this all day. I've tried "match", "find", etc. Since it's a formula, these attemps evaluate the result of the formula and never find the "=". I'm prepared to offer unending :bow: for a solution.

Bob Phillips
05-06-2009, 02:48 PM
Try this

dalea
05-07-2009, 06:59 AM
Bob,

As usual, excellent response. When I don't have the workbook with your response on it the function "NumFormulae" is not on the "Function" drop down list so I assume you wrote a "user defined function", right? If so, how do I access it so I can use it in other workbooks? I would use this all the time when I'm doing budget forecasts. At the beginning of a period, of course all amounts are estimates. As actual data comes in the forcast formula is replaced. To be able to tell me for each line - which is the latest month for which data has been received - is very useful for a number of purposes. Unending :bow: forthcoming upon answer to this question.

Bob Phillips
05-07-2009, 07:34 AM
I created a UDF called NumFormulae.

If you want it available to all workbooks, the best way is to create a specific addin and add the UDF to that. That way it will be available as long as you have the addin installled.

dalea
05-07-2009, 07:48 AM
Bob,

Thanks for your quick reply. Excuse me if I was unclear in my previous post. My question is: How do I get to the code for the UDF you created so that I can do what you suggested in #4? If I can't access it from the workbook, could you please post the code in your response. Thanks.

Bob Phillips
05-07-2009, 07:51 AM
Oh sorry, just goto the VBE (Alt-F11), open the code module named Module2, and it is called NumFormulae.

But it is very simple, this is the code



Function NumFormulae(rng As Range)
Dim cell As Range

For Each cell In rng

NumFormulae = NumFormulae - cell.HasFormula
Next cell
End Function

dalea
05-07-2009, 08:02 AM
As promised: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: :bow: ad infinitum!