CODEWITHSUNDEEP
excelvba
Hana
Hana

Reputation: 81

Method Range of object worksheet failed when copying dynamic range

I have loop that goes over a range of cells and if a condition is met, I copy relevant data to one sheet and if it doesn't met to another. I am having a problem when using dynamically the range in order to copy.

I tried to reference the range in 2 different ways but get the same error

For i = 2 To lastRow
     Set found= WC.Range("O1:O" & LastInList).Find(SL.Range("G" & i))

    If Not (found Is Nothing) 
        SL.Range(Cells(i, 1), Cells(i, 3)).Copy Destination:=WC.Range("A" & WCEmptyRow) 'ERROR here
    Else
        'do something
    End If
Next i

Also tried:

SL.Range("A" & i, "C" & i).Copy Destination:=WC.Range("A" & WCEmptyRow) ' ERROR here

How should I reference the cells in row I in order to copy them?

Upvotes: 1

Views: 45

Answers (1)

Vityata
Vityata

Reputation: 43585

What is foundAH? It is not in the code. Make sure tahat the check is for If Not (foundAH Is Nothing). In order to avoid undeclared variables, follow the Option Explicit of @PEH from the comments - In the VBA editor go to Tools › Options › Require Variable Declaration

Furthermore, the code falls in number 1 error in the VBA tag in SO - not explicitly declaring parent worksheets. See the dots in the amended code:

For i = 2 To lastRow
    Set found= WC.Range("O1:O" & LastInList).Find(SL.Range("G" & i))

    If Not (found Is Nothing) 
        With SL
            .Range(.Cells(i, 1), .Cells(i, 3)).Copy Destination:=WC.Range("A" & WCEmptyRow)
        End With
    Else
        'do something
    End If
Next i

Upvotes: 1

Related Questions