+ Reply to Thread
Results 1 to 10 of 10

macro finding errors in my sheet

  1. #1
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    macro finding errors in my sheet

    Can someone advise how to tweak this code, if possible, to detect another error type in my workbook? Here is the code I am using.

    Please Login or Register  to view this content.
    Example workbook format:

    "MAIN COLUMN HEADER A" would be cell A1, "SUB HEADER 1" would be cell A2, "MAIN COLUMN HEADER B" would be cell B1 and so on in this example:


    MAIN COLUMN HEADER A | MAIN COLUMN HEADER B |
    SUB HEADER 1
    sample data
    sample data


    SUB HEADER 2
    sample data
    sample data
    sample data <<<< must have an empty cell following this sample data cell followed by the new SUB HEADER
    SUB HEADER 3
    sample data
    sample data
    sample data
    sample data
    sample data
    sample data


    SUB HEADER 4
    sample data
    sample data
    sample data


    The error is no empty cell prior to any all CAPS SUB HEADER. any all CAPS SUB HEADER must be preceded by a blank cell which is used to separate different types of info. All of this data is only in column A. I'd just like to be alerted to the errors existence so I can manually inspect it and correct it. Appreciate any assistance with this. Thanks

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: macro finding errors in my sheet

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    Re: macro finding errors in my sheet

    Thank you for providing that to me. It detects the error that I described, but it ceased detecting the other error type I was looking for:

    SUB HEADER 1
    sample data
    sample data

    SUB HEADER 2
    sample data
    <<<< Error - every empty cell should be followed down one cell with an all CAPS SUB HEADER
    sample data
    sample data

    SUB HEADER 3
    sample data
    sample data
    sample data

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: macro finding errors in my sheet

    Your original sample had two blank cells when copied and pasted. The new one seems to have only one separator cell. So is:
    ...
    sample data
    [blank]
    sample data
    ...
    an indication of an error? Post a small sample file.
    Last edited by protonLeah; 06-04-2017 at 06:58 PM.

  5. #5
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    Re: macro finding errors in my sheet

    My apologies, I didnt realize that it pasted that way. Below is the correct format. It is basically an all caps header with the relevant data pertaining to that header directly below then following (directly below) the sample data is a blank cell used to separate the next different group of data and it follows that format for about 100k rows. The main issues I am having is if it doesnt follow this format pattern other things dont work properly. That empty cell separating the previous sample data and the new sub header for the next group of data is vital. I have found errors of blank cells between sample data that belongs together as well as missing sub headers and double blank spaces all of which would mess up a lot of other macros that depend on that format pattern. Also, the sample data varies in how many rows. Sometimes its 5 cells sometimes its 50 cells of like sample data grouped together.

    I hope I described the issue well enough. Thank you again for your time and help with this. I recently lost my partner to cancer and he was a VBA expert and handled all these problems as his part of the work load. I have signed up for a class to try to learn to help myself better, but for now I am just trying to get my arms around fixing these errors. I been trying to get this for a week. If he was here (him knowing exactly what the issue is) he would fix it in 10 minutes.

    All CAPS SUB HEADER
    sample data
    sample data
    sample data
    sample data
    (single blank cell)
    All CAPS SUB HEADER
    sample data
    sample data
    (single blank cell)
    All CAPS SUB HEADER
    sample data
    sample data
    (single blank cell)
    Last edited by overbet; 06-05-2017 at 10:54 AM.

  6. #6
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    Re: macro finding errors in my sheet

    excel_error_sample.PNG

    attached example
    Last edited by overbet; 06-05-2017 at 09:36 PM.

  7. #7
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    Re: macro finding errors in my sheet

    error_sample.xlsm

    attached workbook example

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: macro finding errors in my sheet

    See if this is how you wanted.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-24-2016
    Location
    N/A
    MS-Off Ver
    2010
    Posts
    26

    Re: macro finding errors in my sheet

    That is exactly what I needed to find my errors. I tried every possible error I could think of and it caught them all. I cant thank you enough for this.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: macro finding errors in my sheet

    You are welcome and thanks for the rep.

+ 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. Macro creates new sheet but when running it errors.
    By MIchaelb1100 in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 06-07-2013, 08:51 AM
  2. Help in finding errors
    By harishankarin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-13-2012, 12:28 AM
  3. Macro Finding / Finding a Sheet containing
    By DakotaDK810 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2012, 12:17 AM
  4. Replies: 0
    Last Post: 08-18-2012, 03:06 AM
  5. Checking the Syntax Errors in the Excel Sheet Using Macro
    By kollurusk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-19-2012, 08:59 AM
  6. Finding Formula Errors
    By idiotmitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2010, 12:30 PM
  7. Finding errors in a workbook
    By LAF in forum Excel General
    Replies: 3
    Last Post: 11-02-2007, 11:49 AM
  8. Finding errors
    By Daniel - Sydney in forum Excel General
    Replies: 2
    Last Post: 05-29-2006, 01:30 AM

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