对象超出范围错误无缘无故

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

我在VBA宏excel中编写下面的代码,我的问题是我在行(107,col 10)中得到了我们的范围错误的对象,我不知道为什么。这条线我得到了错误

  .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.

我不知道为什么我在代码的第一部分中得到了Object超出范围的错误。

2 个解决方案

#1

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.

您有工作表(“OpenPOsReport”)。在您的代码中激活,然后您尝试选择.Range(.Cells(x,“A”),. Cells(x,“AC”))。在MRPwks上选择不活动那时候。这不可能。

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.

首先选择范围不是必需的。

#2

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.

如果不尝试选择范围,则可以避免此错误(因为您无法在不活动的工作表上选择范围)。一个常见的错误是说“好吧,好吧,那么我只需要添加一个.Activate来确保正确的工作表处于活动状态。但是这会产生意大利面条代码,因为你经常需要跟踪哪个工作簿是哪个工作簿active,使代码难以阅读,难以调试。

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.

在Excel中选择/激活事物几乎不需要,当你以这种方式执行时,它往往会导致各种难以解决的错误,例如你所拥有的错误。

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

#1