vba - 424 需要错误对象

Question

该功能只是为了遍历一大堆工作表并清除旧数据并将其复制到历史范围，以便为实际数据分析和传输做准备。当我尝试重置范围对象以继续清除工作表中的单元格时，我收到“运行时错误'424':需要对象。

我对这个错误特别困惑，因为它适用于它之前的两个对象而没有问题。拼写似乎是正确的。有任何想法吗？ Set historStartRangePuts = historStartRangePuts.Offset(0,10) 行函数底部的 Else 语句发生错误

    Sub ClearTransferHistoricalOI(oiSheet As Worksheet)

    Dim continue As Boolean
    Dim startRangePuts As Range, startRangeCalls As Range, historStartRangePuts As Range, historStartRangeCalls As Range
    Dim newStartRangePuts As Range, newstartRangeCalls As Range, newHistorStartRangePuts As Range, newHistorStartRangeCalls As Range
    Dim columnCounter As Integer

    'Sets start range
    Set startRangePuts = oiSheet.Cells(5, 1)
    Set startRangeCalls = oiSheet.Cells(5, 3)
    Set historStartRangePuts = oiSheet.Cells(5, 6)
    Set historStartRangeCalls = oiSheet.Cells(5, 8)

    continue = True

    oiSheet.Activate

    'Attempts to catch already cleared sheets
    If IsEmpty(startRangePuts) = True Then
        continue = False

    End If

    Do While continue = True

        'Clears puts and calls historical data
        oiSheet.Range(historStartRangePuts, historStartRangePuts.Offset(0, 1)).Select
        oiSheet.Range(Selection, Selection.End(xlDown)).ClearContents

        oiSheet.Range(historStartRangeCalls, historStartRangeCalls.Offset(0, 1)).Select
        oiSheet.Range(Selection, Selection.End(xlDown)).ClearContents

        'Transfers puts and calls current data to historical data cols
        'Puts
        oiSheet.Range(startRangePuts, startRangePuts.Offset(0, 1)).Select
        oiSheet.Range(Selection, Selection.End(xlDown)).Cut historStartRangePuts
        'Calls
        oiSheet.Range(startRangeCalls, startRangeCalls.Offset(0, 1)).Select
        oiSheet.Range(Selection, Selection.End(xlDown)).Cut historStartRangeCalls

        If IsEmpty(startRangePuts.Offset(0, 10)) = True Then
            continue = False

        Else

            Set startRangeCalls = startRangeCalls.Offset(0, 10)
            Set startRangePuts = startRangePuts.Offset(0, 10)
            Set historStartRangePuts = startRangePuts.Offset(0, 5)
            Set historStartRangeCalls = startRangeCalls.Offset(0, 5)


        End If


    Loop


End Sub

Answer 1

您收到错误是因为没有为变量分配任何内容。这里 -

'清除看跌期权和看涨期权历史数据
oiSheet.Range(historStartRangePuts, historStartRangePuts.Offset(0, 1)).Select
oiSheet.Range(Selection, Selection.End(xlDown)).Select
选择.清除内容

        oiSheet.Range(historStartRangeCalls, historStartRangeCalls.Offset(0, 1)).Select
        oiSheet.Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents

你失去了两个 histor 的定义范围。所以当你尝试

Set historStartRangePuts = historStartRangePuts.Offset(0, 10)
Set historStartRangeCalls = historStartRangeCalls.Offset(0, 10)

没有什么可以引用的。我想是因为 clearcontents返回 variant - 这是一个对象。

它发生在这里 -

    historStartRangePuts.Select
    ActiveSheet.Paste

删除该程序，您将看到 Puts有效，但 Calls仍然失败。