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,209
    Articles
    57
    Blog Entries
    14
    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
  •