+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Return 1 or 0 if a string is found in a series of sheets

  1. #1
    Registered User
    Join Date
    03-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Return 1 or 0 if a string is found in a series of sheets

    Here is what I need to do and I have wasted all sunday on it so help would appreciated.

    I have 5 different (list) of words that I am searching for within a workbook and if they appear I would like some function to return a 1 below the word for each different sheet.

    So here goes the code that does NOT work and I have not even gotten to the list of words yet.
    Sub Find()
    For Each Sheet In Sheets
    Range("v70:v120").Select


    Selection.Find(What:="value", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    Next Sheet

    end sub
    This only finds the word "value" it does not return a 1 or a 0.
    Furthermore to returning a 1 or a 0
    I would like the function to return on which sheet name it was found.

    Thank you so much for any help.

    Maybe I can explain this better: I have 1 sheet with 5 words spaced horizontally: Value Dollar Cash Euro Dollar
    The workbook has 20 sheets lets say whose names are on the left of the sheet vertically
    I want the function to cycle through each sheet and search in a range for each one of those words and if they appear to put in a 1 below the word if they occur on a sheet.
    Last edited by gbullr; 04-12-2010 at 12:44 AM. Reason: Forgot quote tags

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Return 1 or 0 if a string is found in a series of sheets

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Return 1 or 0 if a string is found in a series of sheets

    I don't think you need a macro for this.

    Please Login or Register  to view this content.
    With this layout, put this formula in B2 and copy down / across the table...a "1" will appear for every sheet where the word in row 1 is found on that sheet in that range:

    =--ISNUMBER(MATCH("*" & B$1 & "*", INDIRECT("'" & $A2 & "'!V70:V120"), 0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Return 1 or 0 if a string is found in a series of sheets

    The below is a VBA approach but which closely resembles JB's approach:

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 04-12-2010 at 02:38 AM. Reason: removed vCrit as not used...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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