object out of range error for no reason

Question

I am writing the code below in VBA macro excel, my problem is that I get the object our of range error in the line (107, col 10) and I don't know why. the line I get the error

  .Range(.Cells(x, "A"), .Cells(x, "AC")).Select

my code is below

    Sub MRP()
'
' Macro1 Macro
'

'
      Dim wks As Worksheet
      Dim OPwks As Worksheet
      Dim MRPwks As Worksheet
      Dim OPDwks As Worksheet
      Dim DbCwks As Worksheet

      Dim x As Long
      Dim p As Integer, i As Long, q As Long
      Dim a As Integer, m As Integer, k As Long

      Dim rowRange As Range
      Dim colRange As Range

       Dim LastCol As Long
       Dim LastRowOPwks As Long
       Dim LastRowMRPwks As Long
       Dim LastRowDBCwks As Long

       Set MRPwks = Worksheets("MRP")
       Set OPwks = Worksheets("OpenPOsReport")
       Set DbCwks = Worksheets("CompDB")

       Set wks = ActiveSheet
       Worksheets("OpenPOsReport").Activate

       LastRowMRPwks = MRPwks.Cells(MRPwks.Rows.Count, "A").End(xlUp).Row
       LastRowOPwks = OPwks.Cells(OPwks.Rows.Count, "A").End(xlUp).Row
       LastRowDBCwks = DbCwks.Cells(DbCwks.Rows.Count, "A").End(xlUp).Row

        'Set rowRange = wks.Range("A1:A" & LastRow)

        'For m = 8 To LastRow
        'Cells(m, "N") = 0
        'Next m

        For i = 2 To LastRowDBCwks
            p = 0
            For q = 8 To LastRowOPwks

             If DbCwks.Cells(i, "V") = 0 Then k = 0 Else: k = p / Cells(i, "V")

             If OPwks.Cells(q, "A") = DbCwks.Cells(i, "A") Then
             If OPwks.Cells(q, "D") = 0 Or OPwks.Cells(q, "B") < 1 / 1 / 18 
      Then GoTo Nextiteration Else

                If (OPwks.Cells(q, "C") + DbCwks.Cells(i, "C")) >= 
       (DbCwks.Cells(i, "F") + k) Then
                OPwks.Cells(q, "N").Value = 1
                OPwks.Range(Cells(q, "A"), Cells(q, "N")).Select
                With Selection.Interior
               .Pattern = xlSolid
               .PatternColorIndex = xlAutomatic
               .Color = 255
              .TintAndShade = 0
              .PatternTintAndShade = 0
              End With
              Else
                p = p + OPwks.Cells(q, "D").Value
                    OPwks.Cells(q, "N").Value = 0
                    OPwks.Range(Cells(q, "A"), Cells(q, "O")).Select
                    With Selection.Interior
                   .Pattern = xlNone
                   .TintAndShade = 0
                  .PatternTintAndShade = 0
                End With

                End If
             End If
     Nextiteration:
             Next q
          Next i

     'For q = 8 To LastRow
     '    If Cells(q, "N") = 1 Then
     '              End If
     '              Next

        With MRPwks
     For x = 5 To LastRowMRPwks
            If .Cells(x, "AC").Value > 0 Then
                .Range(.Cells(x, "A"), .Cells(x, "AC")).Select
                With Selection.Interior
               .Pattern = xlSolid
               .PatternColorIndex = xlAutomatic
               .Color = 255
              .TintAndShade = 0
              .PatternTintAndShade = 0
               End With
               End If
              If .Cells(x, "AC") = 0 Then
            .Range(.Cells(x, "A"), .Cells(x, "AC")).Select
                    With Selection.Interior
                   .Pattern = xlNone
                   .TintAndShade = 0
                  .PatternTintAndShade = 0
                End With
            End If

            Next x

            End With

     End Sub

I dont know why I get the Object out of range error in the first part of the code.

Do you have `Option Explicit` at the top? Might just be a typo in a variable name somewhere. — barvobot, Jul 27 '18 at 16:13
Error on line 107 column 10? Erm.... the code doesn't have line numbers so if I paste into Notepad++ before I removed the white space then line 107 is the final `Next X` in the code? Should `Then GoTo Nextiteration Else` and `(DbCwks.Cells(i, "F") + k) Then` be on lines by themselves? None of the worksheet variables seem to be defined. Could you have a read of [MCVE] please. — Darren Bartrup-Cook, Jul 27 '18 at 16:30
When you get the errror, hit "Debug" - which line is highlighted? Line numbers are no real use here... — Tim Williams, Jul 27 '18 at 16:32
When I paste your code in to Notepad (and after correcting for two misplaced line breaks) there are only 107 lines of code. You need to better identify which line raises the error. — David Zemens, Jul 27 '18 at 16:33
@cybernetic.nomad I got the error on .Range(.Cells(x, "A"), .Cells(x, "AC")).Select — Shahin Taghikhani, Jul 27 '18 at 16:34
@DarrenBartrup-Cook so where I get the error is on . .Range(.Cells(x, "A"), .Cells(x, "AC")).Select — Shahin Taghikhani, Jul 27 '18 at 16:36
@TimWilliams sorry for my lack of experience, the line I get the error is .Range(.Cells(x, "A"), .Cells(x, "AC")).Select — Shahin Taghikhani, Jul 27 '18 at 16:37
@DavidZemens I get the error on line .Range(.Cells(x, "A"), .Cells(x, "AC")).Select — Shahin Taghikhani, Jul 27 '18 at 16:38
My guess is that you really don't mean `OPwks.Cells(q, "B") < 1 / 1 / 18`. Note that `1/1/18` is the number 1/18 not the date January 1, 2018. — John Coleman, Jul 27 '18 at 16:38
I@JohnColeman just fixed the date with the date (1/1/18) or will fix the it with the value of 43101 which is the number value of 1/1/18 but thats not where I get the error — Shahin Taghikhani, Jul 27 '18 at 16:41
what value of `x` when the error occurs? (though I'm not sure if that will be useful to know, the line of code doesn't look like it would throw that error) — David Zemens, Jul 27 '18 at 16:44
@DavidZemens dont know how to debug it for me to show the value of x. but when I just run that part in another macro it runs ok, but in this it gives me that error — Shahin Taghikhani, Jul 27 '18 at 16:56
You need to learn how to debug. Start here: http://www.cpearson.com/excel/debuggingvba.aspx — David Zemens, Jul 27 '18 at 16:57
@ShahinTaghikhani Doesn't look like it was the issue in this instance, but you just type in `Option Explicit` above your `Sub` declaration and it basically forces you to declare every variable used in the macro. It's Best Practice™ to use it all the time but ymmv — barvobot, Jul 27 '18 at 17:41

score 2 · Answer 1 · answered Jul 27 '18 at 16:54

2

You have Worksheets("OpenPOsReport").Activate in your code, then you try to select .Range(.Cells(x, "A"), .Cells(x, "AC")).Select on MRPwks which is not active at that time. This is not possible.

Change your code to

With MRPwks
    For x = 5 To LastRowMRPwks
        If .Cells(x, "AC").Value > 0 Then
            With .Range(.Cells(x, "A"), .Cells(x, "AC")).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
        If .Cells(x, "AC") = 0 Then
            With .Range(.Cells(x, "A"), .Cells(x, "AC")).Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If

    Next x

End With

It is not neccessary to select the range first.

answered Jul 27 '18 at 16:54

Storax

11,158
3
16
33

I deleted Worksheets("OpenPOsReport").Activate part and again got "method select of object range failed" error – Shahin Taghikhani Jul 27 '18 at 17:00
You did not understand. I did not tell you to remove `.activate`. I did tell you to remove select. – Storax Jul 27 '18 at 17:02
Ok ur answer was half loaded – Shahin Taghikhani Jul 27 '18 at 17:03
Even in my first half loaded part I did not tell you to remove `.activate`. You still do not understand what you are doing when activating a sheet first and then selecting a range on another sheet. – Storax Jul 27 '18 at 17:05
I got your point, am reading Selecting/Activating things in Excel is almost never necessary article, and also got your point and my code worked, that active part was unnecessary thats why I commented out. Thank you very much – Shahin Taghikhani Jul 27 '18 at 17:09

score 1 · Answer 2 · answered Jul 27 '18 at 16:57

You can avoid this error if you don't try to Select the range (because you cannot select a range on a sheet that's inactive). One common mistake is to say "OK, well, then I'll just add a .Activate to make sure the right sheet is active. But that leads to spaghetti code, as you constantly need to keep track of which sheet in which workbook is active, makes the code hard to read and harder to debug.

Selecting/Activating things in Excel is almost never necessary, and when you do it this way it tends to cause all sorts of difficult-to-troubleshoot errors, like the one you have.

Dim rngToFormat as Range

For x = 5 To LastRowMRPwks
    Set rngToFormat = .Cells(x, "A").Resize(1,29)
    If rngToFormat.Cells(29).Value > 0 Then 
        With rngToFormat.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        With rngToFormat.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
Next x

object out of range error for no reason

2 Answers2