424 Error Object Required

Question

This function is just meant to run through a whole bunch of sheets and clear out old data and copy it to a historical range in preparation for actual data analysis and transfer. When I attempt to reset the range objects in order to continue the execution of the clearing of cells in the sheet I get a "Run-time error '424': Object required.

I am particularly perplexed by this error because it works for the two objects before it without a problem. Spelling appears to be proper. Any ideas? Error is occurring in the Else statement at the bottom of the function on the line Set historStartRangePuts = historStartRangePuts.Offset(0,10)

    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

hard to say exactly what is wrong, but 2 suggestions. Since the code does not return any values make it a `Sub`, not a `Function`, since it is not one. The other is to remove `Select` and `ActiveSheet` and work directly with the objects. This is probably the number 1 reason for coding not functioning properly and can easily be avoided. (see [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Scott Holtzman, Feb 23 '16 at 18:07
I am working to remove those and have been successful aside from the initial selecting of the start ranges. Any other thoughts? Still received error — StormsEdge, Feb 23 '16 at 18:14
Neither of the `Histor` variables have a value when you set them at the end. So it can't reference itself. — Raystafarian, Feb 23 '16 at 18:24
Hi @Raystafarian thanks for that. Where do they lose their value if I do not set them to nothing? — StormsEdge, Feb 23 '16 at 18:26
Usually functions are used to change values, not copy and paste ranges. — Davesexcel, Feb 23 '16 at 18:30

Raystafarian · Accepted Answer · 2016-02-23T19:15:30.103

2

You're getting the error because there's nothing assigned to the variable. Here - 'Clears puts and calls historical data oiSheet.Range(historStartRangePuts, historStartRangePuts.Offset(0, 1)).Select oiSheet.Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents

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

You lose the definition of both histor ranges. So when you try to

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

There's nothing to reference. ~~I think it's because clearcontents returns a variant - which is an object.~~

It's happening here -

    historStartRangePuts.Select
    ActiveSheet.Paste

Remove that procedure and you'll see Puts works, but Calls still fails.

edited Feb 23 '16 at 19:15

answered Feb 23 '16 at 18:27

Raystafarian

2,902
2
29
42

The code I edited in above is working properly. I am using the start ranges that are not Cleared in order to drive the locations. – StormsEdge Feb 23 '16 at 18:38
So far as I know, you do not lose a [Range object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx) with a [Range.ClearContents method](https://msdn.microsoft.com/en-us/library/office/aa223828%28v=office.11%29.aspx). However, you would with a [Range.Delete method](https://msdn.microsoft.com/en-us/library/office/ff834641.aspx). – Feb 23 '16 at 18:38
It's normally bad practice to fix code once you get an answer - then nobody that comes by can see what changed. – Raystafarian Feb 23 '16 at 18:39
@Jeeped that's why I said *think*, it was a guess - why would it be doing that at .clearcontents? – Raystafarian Feb 23 '16 at 18:41
I really cannot say. Even the Range.Cut operations are not going to wipe out a `Set ` range object. – Feb 23 '16 at 18:42
@Jeeped maybe it's because (s)he's taking a variable 'startRangePuts` and pasting it over where `historStartRangePuts` is defined? – Raystafarian Feb 23 '16 at 19:09
No, that shouldn't be it. I strongly suspect that there is something we are not privy to and only a close examination by an experienced debugger will show what the true problem is. – Feb 23 '16 at 19:20

424 Error Object Required

1 Answers1