+ Reply to Thread
Results 1 to 7 of 7

Run-time error'1004': Unable to get the Interior property of the Range class

Hybrid View

  1. #1
    Registered User
    Join Date
    09-25-2013
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Run-time error'1004': Unable to get the Interior property of the Range class

    I am encountering an error (Run-time error'1004': Unable to get the Interior property of the Range class) when a(4,1) = MinV.
    Basically, the code runs fine when a(1,1) = MinV or a(2,1) = Min or a(3,1) = MinV. But the run time error occurs when the a(i,m) = MinV condition is true for i = 4

    I have tried debugging but I couldn't figure out why the error occurs at i = 4.



    Dim a, i As Long, FilePath As String, CellD As String, LC As Long, m As Long, LR As Long, MinV
        Dim myRange As Range
     
        LR = Worksheets("Data").Range("AF" & Rows.Count).End(xlUp).Row
     
        a = Worksheets("Data").Range("BB5:BB" & LR)
        m = 1
        Do
            MinV = Application.Min(a)
                
                 For i = 1 To LR - 4
                        If LR <> 5 Then
                        If a(i, m) = MinV Then
                            With Worksheets("Data")
                                .Range(.Cells(i + 4, m + 41), .Cells(i + 4, m + 55)).Interior.ColorIndex = 35
                            End With
                        End If
                        End If
                    Next
                
            Loop While Worksheets("Sheet2").Cells(i, m) = ""
    End sub
    Thanks for your help!

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    Are there merged cells in row 8?

  3. #3
    Registered User
    Join Date
    09-25-2013
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    No. There are no merge cells in row 8

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    May you provide the workbook? The code is correct so the problem must lie with the sheet.

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    There are a lot of unused and partially dim'd variables. Is this a small part of a bigger project?

    Is there any data in column AF?

    e/ Plus your do loop runs the risk of a stack overflow
    Last edited by Solus Rankin; 12-06-2013 at 10:44 AM.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  6. #6
    Registered User
    Join Date
    09-25-2013
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    The code is just a part of a major procedure that is why there are a lot of dim variables.
    Yes I have data in AF

  7. #7
    Registered User
    Join Date
    09-25-2013
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Run-time error'1004': Unable to get the Interior property of the Range class

    Thanks for your help everyone.
    I changed the last line of the code from
    Loop While Worksheets("Sheet2").Cells(i, m) = ""
    to
    Loop While Worksheets("Data").Cells(i, m) <> ""
    Everything seems to work now. I will let you know if I encounter any future issues

+ 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. [SOLVED] Run-time error '1004' Unable to set the Locked Property of the Range Class
    By medakiva in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2013, 09:13 AM
  2. [SOLVED] Run time error 1004: Unable to set hidden property of range class
    By SAVIOBLUEFOX in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2013, 10:38 AM
  3. Run-time error '1004' Unable to set the Locked Property of the Range Class
    By Jerums in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-04-2013, 04:22 PM
  4. Help Please w/ Run-time error '1004' Unable to set the Hidden property of the Range Class
    By ExcelNewbie2534 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2012, 07:38 PM
  5. Run-time error 1004: Unable to set the hidden property of the Range class.
    By seabee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2011, 05:09 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