时间戳

首页 > 解决方案 > 无缘无故的对象超出范围错误

vba - 无缘无故的对象超出范围错误

问题描述

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

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

我的代码在下面

    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

我不知道为什么在代码的第一部分中出现 Object out of range 错误。

标签： vbaexcel

解决方案

您Worksheets("OpenPOsReport").Activate在代码中有，然后您尝试选择 当时不活动的.Range(.Cells(x, "A"), .Cells(x, "AC")).Select。MRPwks这是不可能的。

将您的代码更改为

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

不必先选择范围。

推荐阅读