Results 1 to 2 of 2

Thread: Msgbox reports first #N/A it encounters in the entire workbook.

  1. #1

    Msgbox reports first #N/A it encounters in the entire workbook.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have posted this at Answers.Microsoft. I don't seem to be able to get the solution I need.

    http://answers.microsoft.com/en-us/o...b-2b280bd57fcd

    I have a workbook where I run quite a bit of code and copy a bunch of user-input data to arrange it for import (performed by SQL script).

    Great.

    Except the user needs to copy some data to another worksheet and use it there, too. Then...they see a typo and fix it. Now, I am trying to find ID's I've assigned and I can't because my vlookup now throws a #N/A.

    So, when they try to finalize the data for import, I want to throw some error-checking in up front so they know they've got #N/As to fix. Most have learned how to fix it, but when the data is large, it can be a pain to check for it, so I want to check for it automatically.

    The first #N/A that the code comes across should throw up a msg box that says "Error found at Sheet1!A1" or similar, and a button to go to that cell. Everything is halted, the user fixes the #N/A and runs the finalize again. The next #N/A will throw up a msgbox. And so on until no #N/As are found. If that's the case, the msgbox does NOT come up, my procedure will just call the next procedure.

    So, something like:
    Sub Finalize()
    Call FindNAs
    Call FinalizeMe
    End sub

    I need the FindNAs part.



    I am happy to email my real file to anyone on this board.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Send it to me, Anne. I'll take a look.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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