+ Reply to Thread
Results 1 to 7 of 7

So close! If file name isnt found in folder search, leave cell alone, and continue search

  1. #1
    Registered User
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    75

    So close! If file name isnt found in folder search, leave cell alone, and continue search

    Okay so I'm soo close to accomplishing what I want.

    This code opens a form, searches folders and subfolders, then edits the cell values based on search results.

    The only issue is, if it doesn't find the file in its search results it still edits the cell. I never wrote any of this code, I cant write code. I have been helped on this forum, and various others, to get this far. I've just lost communication with those who have helped thus far.

    Can someone please tell me how to make this code not edit cell if no matches are found in the search.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,907

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea

    Yes you should provide a link to the other thread when cross-posting, though we try to discourage the practice generally speaking.
    Sorry you have not had a reply yet, I suppose your second post was there to "encourage" us to answer... it is unusual to have to wait long, but also, it is almost always better not to follow up yourself, because threads with 0 replies are more often checked by those who help, since we often assume those with replies have someone helping already.

    It looks to me like when the file is not found, that CheckFolders comes back as False? If so then just add an If statement that runs the cell update only when CheckFolders is true.

    If I have misread something in the code, then sorry... it is sometimes hard to follow the code without a sample file to step through.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    75

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea

    Mostly everything is sorted out, except one vital issue.

    worksheet contains:
    Column A with an 11 digit number followed by -1 (referenced in this message as "xxx" for the 11 digit number, and "-#" respectively) [column A contains: "xxx" & "-1"]

    What I hope to achieve
    Code to search folders and subfolders for the 11 digit number ("xxx") if found replace the cell contents with the next available "-#".

    example
    Column A contains:
    05022017001-1
    04072017001-1
    17062017001-1

    Code searches folders and finds:
    05022017001-1, 052017001-2, 052017001-3
    04072017001-1
    and doesn't find any files named 17062017001-1

    Code changes Column A contents to:
    05022017001-4
    04072017001-2
    17062017001-1 (It didn't find any 17062017001-1 so it left it alone)

    what I currently have is
    The code below which searches folders and replaces cell contents in column A properly (there will never be an instance where anything greater then -6 is needed so it starts there and reverse searches [I think])

    The problem with the code below
    Everything works perfect, except if it doesn't find any instances of the "xxx" number in any folders or subfolders it still changes column A contents to "xxx-2"

    What I need
    Code to be changed so that If it doesn't find any "xxx" in folders and subfolders it leaves contents of Column A alone and moves on to the next Row in Column A

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    75

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea


  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,907

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea

    Without a file, it is not easy to follow the code step by step, but I'm thinking this may help... (added lines are in red):

    Please Login or Register  to view this content.
    Last edited by Arkadi; 07-18-2017 at 01:15 PM.

  6. #6
    Registered User
    Join Date
    03-17-2017
    Location
    Alberta, Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    75

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea

    Doesn't make any changes to any cells ever.

    I'm not sure what kind of file you want?

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,907

    Re: So close! If file name isnt found in folder search, leave cell alone, and continue sea

    I just realized the checkfolders function never sets the variable to true. In addition to the changes I already posted, add this line right after lMaxCount = i inside the CheckFolders Function:
    Please Login or Register  to view this content.
    Hope that fixes it

+ 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] Folder search and copy when it found the match
    By misteyoso in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2017, 08:40 AM
  2. [SOLVED] Search all workbooks in a specified folder and return the desired cell value and file name
    By Ravana in forum Excel Programming / VBA / Macros
    Replies: 45
    Last Post: 05-08-2015, 05:31 AM
  3. Search Folder + Search Excel File and Sum
    By Vanda_a in forum Excel General
    Replies: 1
    Last Post: 03-06-2015, 12:00 PM
  4. Replies: 2
    Last Post: 03-17-2014, 04:19 PM
  5. Macro: Search for a file name in a specified folder and link to excel cell
    By jr13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2013, 03:57 PM
  6. Replies: 10
    Last Post: 06-04-2012, 09:38 PM
  7. Search column and if results found put an X if not leave blank
    By slo5oh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2011, 09:32 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