excel - vba 运行时错误“1004”-对象“_global”的方法“范围”失败

Question

下面的代码正在将数据从 B2 复制到 Excel 表 B 列的 B114 单元格。

在要复制的范围中添加 B117 单元格时，出现“运行时错误'1004'-对象'_global'的方法'范围'失败错误。

B117 被计算为从 B 列复制的第 53 个单元格，因为我跳过了一些我不想复制的单元格。

如果我从范围中删除 B117 单元格，那么这段代码运行良好，没有任何错误，有人知道我犯了什么错误吗？

我想在范围内添加 B117、B118、B119 和更多单元格。

Sub AddEntry()
Range("B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21, B22, B23, B24, B25, B26, B27, B28, B31, B32, B34, B35, B36, B37, B38, B48, B50, B51, B52, B57, B64, B68, B72, B76, B78, B85, B92, B96, B100, B104, B108, B112, B114, B117").Copy
Sheets("Horizontal_Table").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False End Sub

标签： excelvba

Answer 1

它总是数组

再次嗨，更好的方法当然是使用数组。您读取一些数据，将其复制到数组中，进行一些计算并BAMMM！，立即将数组写入范围。你得试试这个。

Option Explicit
'QuestionTitle: vba Run-Time error '1004'- Method 'Range' of object '_global' failed
'QuestionLink: https://stackoverflow.com/questions/52220889/vba-run-time-error-1004-method-range-of-object-global-failed/52226045
Sub AddEntryArray()
'Variables
On Error GoTo VariablesErr
    'Use constants for names:
    Const cwsV As String = "Vertical_Table"
    Const cwsH As String = "Horizontal_Table"
    'Use constants for separators
    Const cSep As String = ","
    'Workbooks, worksheets, ranges etc.
    Dim wb As Workbook
    Dim wsV As Worksheet
    Dim wsH As Worksheet
    Dim raC As Range
    Dim raR As Range
    Dim arrV As Variant
    Dim i1 As Integer
    Dim iF1 As Integer
    'A range can be declared by a string, so use string variables:
    Dim strV As String
    Dim strRight As String
    Dim strLeft As String
'Assign
On Error GoTo AssignErr
    Set wb = Application.ActiveWorkbook
    Set wsV = wb.Worksheets(cwsV)
    Set wsH = wb.Worksheets(cwsH)
    'New Entries here, you can put in spaces or $ signs but every entry has to
    'be a CELL range. You can't use B2:B4.
    strV = "B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,"
    strV = strV & "B12,B13,B14,B15,B16,B17,B18,B19,B20,B21,"
    strV = strV & "B22,B23,B24,B25,B26,B27,B28,B31,B32,B34,"
    strV = strV & "B35,B36,B37,B38,B48,B50,B51,B52,B57,B64,"
    strV = strV & "B68,B72,B76,B78,B85,B92,B96,B100,B104,B108,"
    strV = strV & "B112,B114"
    strV = strV & ""
    strV = strV & ""
    strV = strV & ""
'Program
On Error GoTo ProgramErr
    strRight = strV
    'Count number of cSeps (Commas in this case)
    Do Until InStr(1, strRight, cSep) = 0
        i1 = i1 + 1 'cSep found
        strRight = Right(strRight, Len(strRight) - InStr(1, strRight, cSep))
    Loop
    'There is one last entry after the last cSep therefore: i1 + 1 as UBound
    ReDim arrV(1 To 1, 1 To i1 + 1) As Variant
    strRight = strV
'Remarks: When using arrays they are usually two dimensional: rows & columns.
  'The first dimension representing the rows (fields, records...) is usually
  'used for the records, so without a second thought, LBound or UBound are used
  'with one argument only (Name of the array) e.g. LBound(arrV). But these
  'functions have a second argument namely the dimension which is by default or
  'if omitted equal to 1. So if you want to address the number of columns, you
  'have to use the second argument: LBound(arrV,2) or UBound(arrv,2).
    'Write the values of ranges to an array (the 2 stands for the 2nd dimension)
On Error GoTo ForNextErr
    For iF1 = LBound(arrV, 2) To UBound(arrV, 2)
        Select Case iF1
          Case Is < UBound(arrV, 2)
            'The left part is one range which is written to the array.
            strLeft = Trim(Left(strRight, InStr(1, strRight, cSep) - 1))
            arrV(1, iF1) = wsV.Range(strLeft).Value
'            Debug.Print iF1 & " - " & arrV(1, iF1)
            'The right part is what is left of the string and is processed again
            'in the next case of iF1
            strRight = Trim(Right(strRight, Len(strRight) - _
              InStr(1, strRight, cSep)))
          Case Else 'arrived at the last range, there is no cSep left
            arrV(1, iF1) = wsV.Range(strRight).Value
'            Debug.Print iF1 & " - " & arrV(1, iF1)
        End Select
    Next iF1
'Remarks: Above in the ForNext loop the Trim function isn't even needed. Looks
  'like Range trims the range values itself.
    'Determine the target range
    Set raC = wsH.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
'Remarks: raC is only the target cell, and we could loop now through the array
  'and write cell by cell to the target worksheet. But if we 'd know the range
  'we could write the array much faster to the range directly: range = array.
    'Determine target range
On Error GoTo TargetRangeErr
    Set raR = wsH.Range(Cells(raC.Row, raC.Column).Address & ":" & _
      Cells(raC.Row, raC.Column + i1).Address)
    'Write from array to worksheet (target range)
    raR = arrV
    GoTo ProgramEnd
VariablesErr:
    MsgBox "Something's wrong in Variables."
    GoTo ProgramEnd
AssignErr:
    MsgBox "Something's wrong in Assigning."
    GoTo ProgramEnd
ProgramErr:
    MsgBox "Something's wrong in Program."
    GoTo ProgramEnd
ForNextErr:
    MsgBox "Something's wrong in the ForNext loop."
    GoTo ProgramEnd
TargetRangeErr:
    MsgBox "Something's wrong with determining the target range."
    GoTo ProgramEnd
ProgramEnd:
End Sub

我更喜欢这个。问候。