Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: function that can search within cell formula, not cell value

  1. #1
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Al

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

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






    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    New Member
    Join Date
    Jun 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    New Member
    Join Date
    Jun 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •