+ Reply to Thread
Results 1 to 5 of 5

Macro to check for missing data and copy data if none missing

Hybrid View

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    96

    Macro to check for missing data and copy data if none missing

    Okay. I have some code that loops through a column of data and pops up a message box if it finds an empty cell

    
    Sub CheckDataEntered()
    
    Sheets("Input Data").Select
    
            [c3].Select
                
            Do Until ActiveCell = ""
        
            ActiveCell.Offset(0, 1).Select
            
            Loop
            
                ActiveCell.Offset(0, -1).Select
           
            Do Until Selection.Row = 40
       
            If ActiveCell = "" Then
    
            MsgDataNotEntered
    
            Exit Do
            
            Else: ActiveCell.Offset(1, 0).Select
        
            End If
            
            Loop
            
    End Sub
    Clicking OK in the message box stops the code and tells the user that some data is missing. I have another macro (CopyData) that copies the data into another sheet. What I am trying to do is to get the CopyData macro to run in the above code. E.g. Check the column of data_if data is missing, stop all macros and show message box_if no data is missing, run CopyData macro. I hope this makes sense.
    Last edited by rosshkerr; 03-03-2012 at 05:23 PM.
    Cheers

    Ross

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Macro to check for missing data and copy data if none missing

    Try this.
    If there is an error, the macro goes to the end of it otherwise it will hit the CopyData line that will run it.
    Sub CheckDataEntered()
    
    Sheets("Input Data").Select
    
            [c3].Select
                
            Do Until ActiveCell = ""
        
            ActiveCell.Offset(0, 1).Select
            
            Loop
            
                ActiveCell.Offset(0, -1).Select
           
            Do Until Selection.Row = 40
       
            If ActiveCell = "" Then
    
            MsgDataNotEntered
    
            GoTo Err_End
            
            Else: ActiveCell.Offset(1, 0).Select
        
            End If
            
            Loop
            CopyData 'Runs the copy data macro
    
     :Err_End
    End Sub

  3. #3
    Registered User
    Join Date
    01-11-2012
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Macro to check for missing data and copy data if none missing

    I tried to run this and got a compile error: sub or function not defined

  4. #4
    Registered User
    Join Date
    01-11-2012
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Macro to check for missing data and copy data if none missing

    All sorted. I changed:

    :Err_End
    to

    Err_End:

  5. #5
    Registered User
    Join Date
    01-11-2012
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: Macro to check for missing data and copy data if none missing

    Thank you very much, headache cleared

+ 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