Copy Paste Issue in VBA; Object required error but Excel is finding the object.

arg123

New Member
Joined
Jun 8, 2016
Messages
28
Hello all, and thank you in advance for spending any time at all on this, likely, very simple oversight on my part.

I am getting the object required error during a simple copy and paste. Odd thing, the object it cannot find is highlighted and ready to be copied.
You may notice some object definition redundancies from trying a few different ways of navigating the perceived problem.

Any assistance or feedback would be greatly appreciated.

VBA Code:
Dim sampidwrklst As Range
Set sampidwrklst = Sheets("AST Worklist").Range("C4:C2000")
Dim i As Long

For i = 1996 To 1 Step -1

    worklist = sampidwrklst.Cells(i).Value
    'casewrklst = sampidwrklst.Cells(i).Offset(0, -1)
   
    If worklist = "" Then
    ElseIf worklist <> "" Then
       
        Dim sampidorders As Range
        Set sampidorders = Sheets("Orders").Range("A2:A400")
        Dim j As Long
       
            For j = 399 To 1 Step -1
           
                orders = sampidorders.Cells(j).Value
                'caseorders = sampidorders.Cells(j).Offset(0, 1)
               
                If orders = "" Then
                ElseIf InStr(1, orders, worklist) > 0 Then
                    Sheets("Orders").Activate  'the sheet activation was an attempt to be VERY specific in the code as I thought the sheets not activating may be the issue...seems not
                    orders.Offset(0, 1).Select  'this is the error location in the code, but this cell is found and highlighted at the time of error -- odd
                    Selection.Copy
                    Sheets("AST Worklist").Activate
                    worklist.Offset(0, -1).Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Else
                End If
            Next j
    Else
    End If

Next i

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
To copy worksheet ranges it is not necessary to activate worksheets or select worksheet ranges. That the correct cell was selected at the time of the copy attempt is purely coincidental.

The error occurs because the variable named orders is not of any object type. This variable is not explicitly declared in your code and since the content of a cell is being assigned to it we know for sure it doesn't refer to an object. FYI, with the mentioned value assignment VBA will implicitly declare this variable. The final type (String, Double, Date, Currency, ...) depends on both the cell content and the cell format.

I've put some comments in your code to make more visible that some comparisons are superfluous. The snippet in which the copy and paste attempt takes place can probably be replaced by one line of code. Note that my suggestion should only be taken as an example. In the current program flow my suggestion makes no sense at all because the same cell would be overwritten 399 times in succession.

Because I can't figure out the logic of your code I don't have a working alternative for you at this point.

VBA Code:
Sub arg123()
    Dim sampidwrklst As Range
    Set sampidwrklst = Sheets("AST Worklist").Range("C4:C2000")
    Dim i As Long

    For i = 1996 To 1 Step -1

        worklist = sampidwrklst.Cells(i).Value
        'casewrklst = sampidwrklst.Cells(i).Offset(0, -1)
   
        If worklist = "" Then
            '>> do nothing (& branche to End If)
        
        ElseIf worklist <> "" Then
       
            Dim sampidorders As Range
            Set sampidorders = Sheets("Orders").Range("A2:A400")
            Dim j As Long
       
            For j = 399 To 1 Step -1
           
                orders = sampidorders.Cells(j).Value
                'caseorders = sampidorders.Cells(j).Offset(0, 1)
               
                If orders = "" Then
                    '>> do nothing
                ElseIf InStr(1, orders, worklist) > 0 Then
'                    Sheets("Orders").Activate    'the sheet activation was an attempt to be VERY specific in the code as I thought the sheets not activating may be the issue...seems not
'                    orders.Offset(0, 1).Select   'this is the error location in the code, but this cell is found and highlighted at the time of error -- odd
'                    Selection.Copy
'                    Sheets("AST Worklist").Activate
'                    worklist.Offset(0, -1).Select
'                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                    '>> REPLACEMENT:
                    '>> it seems you are trying to do something like this <<
                    sampidwrklst.Cells(i).Offset(0, -1).Value = sampidorders.Cells(j).Offset(0, 1).Value
                Else
                    '>> do nothing
                
                End If
            Next j
        Else
            '>> do nothing
        End If
    Next i
End Sub
 
Upvote 0
Solution
To copy worksheet ranges it is not necessary to activate worksheets or select worksheet ranges. That the correct cell was selected at the time of the copy attempt is purely coincidental.

The error occurs because the variable named orders is not of any object type. This variable is not explicitly declared in your code and since the content of a cell is being assigned to it we know for sure it doesn't refer to an object. FYI, with the mentioned value assignment VBA will implicitly declare this variable. The final type (String, Double, Date, Currency, ...) depends on both the cell content and the cell format.

I've put some comments in your code to make more visible that some comparisons are superfluous. The snippet in which the copy and paste attempt takes place can probably be replaced by one line of code. Note that my suggestion should only be taken as an example. In the current program flow my suggestion makes no sense at all because the same cell would be overwritten 399 times in succession.

Because I can't figure out the logic of your code I don't have a working alternative for you at this point.

VBA Code:
Sub arg123()
    Dim sampidwrklst As Range
    Set sampidwrklst = Sheets("AST Worklist").Range("C4:C2000")
    Dim i As Long

    For i = 1996 To 1 Step -1

        worklist = sampidwrklst.Cells(i).Value
        'casewrklst = sampidwrklst.Cells(i).Offset(0, -1)
  
        If worklist = "" Then
            '>> do nothing (& branche to End If)
       
        ElseIf worklist <> "" Then
      
            Dim sampidorders As Range
            Set sampidorders = Sheets("Orders").Range("A2:A400")
            Dim j As Long
      
            For j = 399 To 1 Step -1
          
                orders = sampidorders.Cells(j).Value
                'caseorders = sampidorders.Cells(j).Offset(0, 1)
              
                If orders = "" Then
                    '>> do nothing
                ElseIf InStr(1, orders, worklist) > 0 Then
'                    Sheets("Orders").Activate    'the sheet activation was an attempt to be VERY specific in the code as I thought the sheets not activating may be the issue...seems not
'                    orders.Offset(0, 1).Select   'this is the error location in the code, but this cell is found and highlighted at the time of error -- odd
'                    Selection.Copy
'                    Sheets("AST Worklist").Activate
'                    worklist.Offset(0, -1).Select
'                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                    '>> REPLACEMENT:
                    '>> it seems you are trying to do something like this <<
                    sampidwrklst.Cells(i).Offset(0, -1).Value = sampidorders.Cells(j).Offset(0, 1).Value
                Else
                    '>> do nothing
               
                End If
            Next j
        Else
            '>> do nothing
        End If
    Next i
End Sub
GW, your notes are spot on. I actually tried your suggestion of the sampidwrklst.Cells(i).Offset(0, -1).Value = sampidorders.Cells(j).Offset(0, 1).Value and it passed the script through but wasn't successful (cells ended up blank). I'll keep playing with it. Thank you again for your time!
 
Upvote 0
Glad to help. If you could explain in more detail what you expect from the code, a solution might come within reach a little faster.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top