function that can search within cell formula, not cell value

[Al Chara]

I did some research into XLM (Excel4) functions. I got a big list of the avialable functions. It is a little overwhelming.

Does anyone have a list of the most useful or helpful XLM functions?

[Al Chara]

Anyone out there know of a built-in Excel function that can search within a cell's formula, not in the cell's value.

[Ivan F Moala]

On 2002-04-12 12:39, Al Chara wrote:
Anyone out there know of a built-in Excel function that can search within a cell's formula, not in the cell's value.

Hi Al

This may get you going...what you then need
to do is a search within the assigned variable for the formula....

Sub Frmula()

Dim FrmRg As Range

Dim rCell As Range



Set FrmRg = [A1].SpecialCells(xlCellTypeFormulas, 23)

For Each rCell In FrmRg

    MsgBox "Formula @ " & rCell.Address & " " & rCell.Formula

    '// Do your thing here

Next



End Sub

[Al Chara]

Ivan,

Thank you for your response, but I know how to do it with VBA. I was just wondering if there was an equivalent worksheet function to SEARCH or FIND that would look at the cells formula instead of the value. I just don't think you can do it without some code.

[Jay Petrulis]

On 2002-04-12 13:10, Al Chara wrote:
Ivan,

Thank you for your response, but I know how to do it with VBA. I was just wondering if there was an equivalent worksheet function to SEARCH or FIND that would look at the cells formula instead of the value. I just don't think you can do it without some code.

Hi Al,

Just a guess, but would there be an Excel4 macro formula you can use, possibly only as a named formula?

Maybe Get.Formula or something like that, although I don't remember where to find a list of all those quirky legacy things.

Jay

[Mark O'Brien]

When you hit CTRL+F in Excel to get the find dialogue box up there are a couple of comboboxes on it. One of these is the "Look In" box. You can choose "Values", "Formulas" or "Comments" there.

[Ivan F Moala]

Hi Al

Using Jays suggestion....you could use a
defined name eg CellFormula and reference
it as;

=GET.CELL(6,Sheet1!$A1)

[Al Chara]

Thanks Mark, but that won't work for me, because I wanted to use it as part of a larger formula.

Ivan and Jay, works perfectly.

Thanks all for your time and help.

Kind regards, Al.

[patshan]

What a great site. I am an advanced beginner and I am having trouble taking the results of a formula and rounding the number down to the nearest hundred. For instance, 1,333 would be rounded down to 1,300 and 526 would be rounded to 500. I use the paste function but keep getting the circular reference. Any help would be greatly appreciated to this self taught user.
Steve

[patshan]

[quote]
On 2002-06-03 14:43, patshan wrote:
What a great site. I am an advanced beginner and I am having trouble taking the results of a formula and rounding the number down to the nearest hundred. For instance, 1,333 would be rounded down to 1,300 and 526 would be rounded to 500. I use the paste function but keep getting the circular reference. Any help would be greatly appreciated to this self taught user.
Steve
PS
=$L$103/A12 This is a copy of the formula in question. As I said, I am an advanced beginner. Sorry for the tagging onto the original question, however I do not see a post button.