+ Reply to Thread
Results 1 to 10 of 10

Searching through values and Formulas in one run

  1. #1
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Searching through values and Formulas in one run

    I often have to conduct a search for a given string, whether in the value of cells or inside the formulas (and searching backwards, from the end of the document to its beginning).
    Presently I cannot but run the search twice: first searching in the values and then in the formulas.
    The first with this code (essentially):
    Please Login or Register  to view this content.
    Which I do by clicking on a CommandButton in a UserForm I have created, once for each occurrence, until I find the one I am looking for.

    The second with this code (essentially):
    Please Login or Register  to view this content.
    by clicking on another CommandButton in my UserForm, once for each occurrence.

    But that double search is awkward and time consuming, for sometimes I must peruse the whole document the first time (through values) and then again (through formulas) part of it or the whole also.
    I wonder if there is a way of searching through both (values and formulas) in one run. That would imply something like this in the code line of my macro:
    Please Login or Register  to view this content.
    I know that syntax is not allowed; but is there some other code to that same effect?

    Thank you for any help.
    Last edited by aca; 03-18-2024 at 05:47 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Searching through values and Formulas in one run

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Re: Searching through values and Formulas in one run

    Thank you very much, protonLeah: your script works perfectly!

    It looks too professional for me to fully understand, but I will study it further to see if I can modify it a little and make it do exactly what I need.

    Yes, because, what I need is not exactly to get a list of all the cells in which the findings occurr (in the cell and in the formula bar);
    I want the cursor to move on and select the cell of each occurrence.
    And when it selects the occurence I am looking for, I want to be able to interrupt the search and do whatever I wanted to do next.
    If you could do me an extra service and change your script to do that, my extra thanks!!!; else, I'ill try my hand at it but with less hope, much less!!!

    Thanks; I'm adding to your reputation.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Searching through values and Formulas in one run

    Try this one
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Re: Searching through values and Formulas in one run

    Thank you so much again !, protonLeah: your script not only works perfectly, but it performs exactly what I need, and almost exactly what I wished.

    Those "almost" and "wished" are because I would wish the search to go from end to beginning (from bottom line to top line and then from right column to left column). That is because the search is for my accounting program, where the later entries of a given datum, say a customer, will be more useful for the present moment than the first ones.
    So, if you are so kind as to try yet an extra effort to fit my wants, I'll be even more grateful than I am already now. But 'else', i'ts all right: this latest script, as it is, essentially meets my needs.

    I also want you to know tbat I think I made a mistake yesterday in trying to add to your reputation: I'm affraid I unwillingly clicked the wrong button and detracted ! from it; I hope my note with the reasons why may have made it clear that I intended to be positive, not negative.
    I'm very sorry for that, and I hope I'll do it wright when I try the same now, in a moment.
    THANK YOU

  6. #6
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Re: Searching through values and Formulas in one run

    Sorry again, protonLeah, about the last line in my latest post: the system tells me this: "You must spread some Reputation around before giving it to protonLeah again". But you know my intent...

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,850

    Re: Searching through values and Formulas in one run

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    I also want you to know tbat I think I made a mistake yesterday in trying to add to your reputation: I'm affraid I unwillingly clicked the wrong button and detracted ! from it; I hope my note with the reasons why may have made it clear that I intended to be positive, not negative.
    You need to put this right: please contact one of our Administrators.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Searching through values and Formulas in one run

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Re: Searching through values and Formulas in one run

    Final thanks, protonLeah, for your tenacious constancy in helping me: now that's it!
    Thanks a world for this code and for what I'm learning from it for similar needs down the road.

    Now I am going to comply with a warning I've deserved from one of the moderators about my mistake in trying to add to your reputation last time.
    ACA

  10. #10
    Forum Contributor
    Join Date
    08-04-2005
    Location
    Madrid, Spain
    MS-Off Ver
    2010
    Posts
    241

    Re: Searching through values and Formulas in one run

    Yes, AliGW? Thank you for contacting me with this warning.
    Following your instructions, I've marked this thread as solved (after one final and successful attempt from protonLeah to help me).

    But I don't know what I must do to right my wrong in trying to add to his reputation previously.

    What is more, now, after he has completely solved my query, I cannot add to it either: I get the same pop-up: "you must spread some Reputation around before giving it to protonLeah again".

    Your guidance for that, will be welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Financial Modeling - Searching for Functions or Formulas to Simplify Forecasting
    By PolyglotLisa in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-18-2024, 11:24 AM
  2. [SOLVED] Need Help with a couple formulas for Searching a document
    By Jede in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2020, 02:06 PM
  3. Replies: 1
    Last Post: 12-19-2017, 01:26 PM
  4. searching for any IP Address Formulas
    By kmb615 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2013, 04:28 PM
  5. Searching For Values
    By ddixonslc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2010, 11:28 PM
  6. Programmatically Searching Formulas for Sheet References
    By Baard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-28-2007, 09:54 PM
  7. [SOLVED] Formulas dealing with searching for characters
    By Electro7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2006, 08:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1