function that can search within cell formula, not cell value

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Anyone out there know of a built-in Excel function that can search within a cell's formula, not in the cell's value.
 
Upvote 0
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....

<pre/>
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
</pre>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi Al

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

=GET.CELL(6,Sheet1!$A1)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top