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

[arg123]

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.

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

 

[GWteB]

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.

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

 

[arg123]

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.

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!

 

[GWteB]

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.