Results 1 to 14 of 14

compile error method or data member not found will not run as a sub

Threaded View

COURTTROOPER compile error method or data... 05-24-2018, 06:44 PM
bakerman2 Re: compile error method or... 05-24-2018, 08:07 PM
COURTTROOPER Re: compile error method or... 05-24-2018, 09:48 PM
bakerman2 Re: compile error method or... 05-24-2018, 10:06 PM
COURTTROOPER Re: compile error method or... 05-25-2018, 12:28 PM
COURTTROOPER Re: compile error method or... 05-25-2018, 12:41 PM
COURTTROOPER Re: compile error method or... 05-25-2018, 01:30 PM
COURTTROOPER Re: compile error method or... 05-25-2018, 04:49 PM
bakerman2 Re: compile error method or... 05-25-2018, 10:27 PM
COURTTROOPER Re: compile error method or... 05-28-2018, 07:00 PM
bakerman2 Re: compile error method or... 05-29-2018, 03:52 AM
COURTTROOPER Re: compile error method or... 06-01-2018, 11:44 AM
bakerman2 Re: compile error method or... 06-01-2018, 12:09 PM
COURTTROOPER Re: compile error method or... 06-05-2018, 12:31 PM
  1. #1
    Forum Contributor
    Join Date
    06-06-2017
    Location
    Phoenix, AZ
    MS-Off Ver
    2013
    Posts
    129

    Arrow compile error method or data member not found will not run as a sub

    I am back needing help again. I have written an error check sub routine to check for blank required cells before the row status is completed. I am getting a compile error, “Method or Data Member not Found” and I don’t understand the Microsoft help file. Could someone help me and explain what I am missing please? The Sign_In sheet is where the data is entered and column “K” is the final status for the row. It will display the fill of the cell green and the ending code will be shown in the cell. For the ending code “ATR” in column “K”, cells in columns “I”, “J”, “L” can’t be blank or empty in that row. The cells missing the data will fill red and the “K” column will turn yellow to indicate a problem in that row. The code worked fine until I changed it to a sub routine. All help is greatly appreciated!

    I have uploaded a sample workbook.

    Here is my code:
    Sub ATR()
    
    Dim Sign_In As Excel.Worksheet
    Set Sign_In = ThisWorkbook.Sheets("Sign_In")
    
         If Sheets.Range("I" & Srn) = "" Then                                   'Error check for column I.
            Sheets.Range("I" & Srn).Interior.Color = RGB(255, 0, 0)            'Changes Fill in column I to red to Inticate empty cell.
            Sheets.Range("K" & Srn).ClearContents                              'Clears contents of Column K if column I is empty.
            Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0)            'Sets background color column K to Red if there is an empty cell that is required.
            MsgBox "Enter DR#, Press OK to enter data in that cell "    'Messge to user on error.
            Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0)          'Sets background color column K to Yellow to indicate incomplete data entry.
                Else                                                    'Error check for column J.
            Sheets.Range("I" & Srn).Interior.Color = Range("A" & Srn).Interior.Color         'Sets shading color to the row shade color.
            Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0)        'Sets background color column K to Green.
               
            If Sheets.Range("J" & Srn) = "" Then                               'Check for empty required cells in row.
                    Sheets.Range("J" & Srn).Interior.Color = RGB(255, 0, 0)        'Changes Fill in column I to red to Inticate empty cell.
                    Sheets.Range("K" & Srn).ClearContents                          'Clears contents of Column K if column I is empty.
                    Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0)        'Sets background color column K to Red if there is an empty cell that is required.
                    MsgBox "Enter Vehicle Removal Report Number, Press OK to enter data in that cell. "
                    Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0)      'Sets background color column K to Yellow to indicate incomplete data entry.
                        Else
                    Sheets.Range("J" & Srn).Interior.Color = Range("A" & Srn).Interior.Color         'Sets shading color to the row shade color.
                    Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0)        'Sets background color column K to Green.
        
        If Sheets.Range("L" & Srn) = "" Then                                       'Check for empty required cells in row.
            Sheets.Range("L" & Srn).Interior.Color = RGB(255, 0, 0)                'Changes Fill in column I to red to Inticate empty cell.
            Sheets.Range("K" & Srn).ClearContents                                  'Clears contents of Column K if column I is empty.
            Sheets.Range("K" & Srn).Interior.Color = RGB(255, 0, 0)                'Sets background color column K to Red if there is an empty cell that is required.
            MsgBox "Enter Hearing Officer, Press OK to enter data in that cell. "
            Sheets.Range("K" & Srn).Interior.Color = RGB(255, 255, 0)              'Sets background color column K to Yellow to indicate incomplete data entry.
                Else
            Sheets.Range("L" & Srn).Interior.Color = Range("A" & Srn).Interior.Color         'Sets shading color to the row shade color.
            Sheets.Range("K" & Srn).Interior.Color = RGB(0, 255, 0)                'Sets background color column K to Green.
        
        End If
            End If
        End If
    End Sub
    Thanks again for any help received.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compile Error: Method or data member not found
    By jtsev13 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-24-2018, 04:07 AM
  2. Compile error Method or data member not found
    By kwesmc1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-30-2016, 02:47 PM
  3. Compile error:Method or data member not found
    By vshukla in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2015, 11:44 AM
  4. Compile Error Method or data member not found
    By ixelister in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2014, 03:39 PM
  5. Compile Error: Method of data member not found (VBA)
    By vbatech in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 03-14-2012, 05:37 PM
  6. Compile Error:Method or Data Member Not Found
    By loknath in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-29-2012, 04:03 AM
  7. [SOLVED] Compile Error Method or data member not found
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-04-2005, 06:05 PM

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