+ Reply to Thread
Results 1 to 10 of 10

VB code help, ignore blanks/specific

  1. #1
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    VB code help, ignore blanks/specific

    Hello all firstly many thanks for taking the time to look at my problem, I have had some help in making this macro, which is a search records macro, to pull/import the records from one sheet to another based on a list of cells. It also tells me if a piece of record is not found. This is very useful! Because I canít then add these records, however it also displays the same message on encountering both blanks and titles. This causes many false positives with regards to needing to enter new records. I have included an example spreadsheet with macro.

    So i would like to be able to add exceptions (blanks/"specific text") to the already supplied macro.

    Any help would be greatly appreciated.

    Best regards

    Alan

  2. #2
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Re: VB code help, ignore blanks/specific

    i think i failed to attatch the relevent spreadsheet
    Attached Files Attached Files

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    40,546

    Re: VB code help, ignore blanks/specific

    Try:

    Please Login or Register  to view this content.

    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Re: VB code help, ignore blanks/specific

    This looks like it should work however i am not sure where i should insert it within the macro. This is what i have tried so far?
    Please Login or Register  to view this content.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    40,546

    Re: VB code help, ignore blanks/specific

    It replaces:

    Please Login or Register  to view this content.
    in your existing code.

    Regards

  6. #6
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Re: VB code help, ignore blanks/specific

    Hmmm i did try that, i replaced and entered the name of the title and it didnt work, as shown above. I still get pop ups for both the titles and the blanks. :S

    Any ideas guys?

    All the best

    Alan

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    40,546

    Re: VB code help, ignore blanks/specific

    I'm not sure why it wasn't/isn't working for you. It seemed fine to me, ignoring "name". It didn't seem to generate a message for a blank entry but I have put a specific test in for this now, just to be on the safe side.

    The full code is now:

    Please Login or Register  to view this content.


    For some reason, the macro code wasn't linked to the button. You may need to right click on the button, select "Assign Macro..." and select "GetData from the list.


    Regards

  8. #8
    Forum Contributor
    Join Date
    12-01-2010
    Location
    Southampton, England
    MS-Off Ver
    Excel 2007
    Posts
    303

    Re: VB code help, ignore blanks/specific

    Unfortunately it still doesn't work, it's ignoring the blanks but not the word "Title"


    Please Login or Register  to view this content.

    Any ideas/fix's for this would be much appreciated.
    All the best

    Alan

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    40,546

    Re: VB code help, ignore blanks/specific

    I don't recall there being a "Title" in the example, nor was it mentioned previously ... is this application being defined on the fly?

    For *your* modification to work, change this:

    Please Login or Register  to view this content.

    to this:

    Please Login or Register  to view this content.

    The LCase does what it says on the tin, converts to lower case ... so you need to compare to lower case text, in this case, "title"


    Regards

  10. #10
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: VB code help, ignore blanks/specific

    Suggestion for future: You can also make text comparisons case-insensitive by adding the line "Option Compare Text" at the very top of a module (i.e. outside of macro) . Then the macro's don't care if text is upper, lower or proper

+ 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