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.

    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).


    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
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    Send it to me, Anne. I'll take a look.
    Ken Puls, FCPA, FCMA, MS MVP

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

    Main Site: -||- Blog: -||- 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