按名称VBA选择工作表时出现运行时错误1004

Question

我一直在为我的公司开发一个Excel宏，该宏打开几个工作簿，分析它们的特定信息行，存储该行，然后在完成每个工作簿后，在两个页面中的一个页面上设置单个工作簿中单元格的水平选择值。我遇到的问题是，在尝试选择第二个页面时，我需要将数据放在上面，我得到一个运行时错误1004。

Sub sortandinsert(listie As Variant)
'Takes in the data array and sorts it as it inserts it into the spreadsheet.
'Expects a 2 dimensional array.
Dim serialarray() As Variant
Dim listlen1 As Integer
Dim listlen2 As Integer
Dim listlen3 As Integer
Dim count1 As Integer
Dim count2 As Integer
Dim SSCcurrentrow As Integer
Dim DSCcurrentrow As Integer
Dim Colstart As Integer
Dim SSCcounter As Integer
Dim DSCcounter As Integer
Dim actbook As Workbook
Dim selectrange As range
Set actbook = ActiveWorkbook
SSCcounter = 0
DSCcounter = 0
Colstart = 1
SSCcurrentrow = 10
DSCcurrentrow = 10
serialarray = FindSerial(listie, serialarray)
listlen1 = findlength(serialarray)
For count = 0 To listlen1 - 1
    MsgBox serialarray(count)
Next
With actbook
    listlen2 = findlength(listie)
    For count1 = 0 To listlen1 - 1
    MsgBox "Current Serial is" & " " & serialarray(count1)
        For count2 = 0 To listlen2 - 1
            If contains(listie(count2), CStr(serialarray(count1))) Then
            listlen3 = findlength(listie(count2))
                If listie(count2)(0) = "SSC" Then
                    Set selectrange = Sheets("SSC").range(Cells(SSCcurrentrow + SSCcounter, Colstart), Cells(SSCcurrentrow + SSCcounter, Colstart + listlen3 - 1))
                    With selectrange
                        .Value = listie(count2)
                    End With
                    SSCcounter = SSCcounter + 1
                ElseIf listie(count2)(0) = "DSC" Then
                    Set selectrange = Sheets("DSC").range(Cells(DSCcurrentrow + DSCcounter, Colstart), Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
                    With selectrange
                        .Value = listie(count2)
                    End With
                    DSCcounter = DSCcounter + 1
                End If
            End If
        Next
        SSCcurrentrow = SSCcurrentrow + SSCcounter + 6
        DSCcurrentrow = DSCcurrentrow + DSCcounter + 6
        'SSCcounter = 0
        'DSCcounter = 0
    Next
End With
End Sub

Set selectrange = Sheets("DSC").range(Cells(DSCcurrentrow + DSCcounter, Colstart), Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
With selectrange
    .Value = listie(count2)
End With     

我可以做什么来修复这个错误？

Answer 1

单元格将引用ActiveWorkbook中的ActiveSheet。这些对象可能没有设置，也可能不在DSC表中。请尝试以下操作：

With actbook.Sheets("DSC")
    Set selectrange = range(.Cells(DSCcurrentrow + DSCcounter, Colstart), .Cells(DSCcurrentrow + DSCcounter, Colstart + listlen3 - 1))
End With

或者，更易读的版本可能是

Set selectrange = actbook.Sheets("DSC").Cells(DSCcurrentrow + DSCcounter, Colstart)
Set selectrange = selectrange.resize(1, listlen)