Avoiding Select in VBA Excel macros with variable ranges

Question

I was following suggestions under question How to avoid using Select in Excel VBA macros, but still getting "Application-defined or object-defined error".

My macro suppose to check value in one of lines, compare it with set value, and if values equals, then copy line to another sheet. I tried to avoid usage of .Select command, but no matter what I try I'm getting errors.

This is my last version:

    Counter2 = 17
    For Counter1 = 12 To 150

    Dim S As Worksheet
    Dim R As Worksheet
    Dim SL As Range
    Dim RL As Range
    Set R = Sheets("Front")
    Set S = Sheets("CHECK LIST")
    Set SL = S.Range(Cells(Counter1, 1), Cells(Counter1, 10))
    Set RL = R.Range(Cells(Counter2, 1), Cells(Counter2, 10))

    Set curCell = Worksheets("CHECK LIST").Cells(Counter1, 6)
    Set checkCell = Worksheets("Front").Cells(3, 5)
        If curCell.Value = checkCell.Value Then

            With S
                .SL.Copy
            End With

            With R
                .RL.PasteSpecial
            End With

            Counter2 = Counter2 + 1

        End If
Next Counter1

Is this something to do with usage of variables (Counter1, Counter2) in Range?

I don't think those with statements are necessary. Neither the dot infront of SL and RL — chancea
This has been answered before many times: qualify both Range and Cells with the worksheet: S.Range(S.Cells(Counter1, 1), S.Cells(Counter1, 10)) and so on — Rory
@Rory's got it -- when you're using ranges, it's best (if not required) to qualify each part of that range. Otherwise, while the Range might be "S.Range" reference, the Cells() part of that might refer to the activesheet, or some other sheet. Best to be explicit. — BruceWayne
possible duplicate of 2 ways for "ClearContents" on VBA Excel, but 1 work fine. Why? — Byron Wall

ChipsLetten ChipsLetten · Accepted Answer · 2015-07-01T21:29:29

The errors are probably being caused by the Copy and PasteSpecial lines in this block of code:

        With S
            .SL.Copy
        End With  
        With R
            .RL.PasteSpecial
        End With

Once you have Set a range variable, Excel knows which worksheet and which workbook contains that range variable. If you want to do something to the range then you do not need to include the worksheet. So, in your code, you just use

SL.Copy
RL. PasteSpecial

It is always a good idea to think if you have any code inside a loop that should be outside. In your code you Set your worksheet variables within the loop but this should be done once before the loop. Adding in the comments made to your post, your code should be changed to:

Dim S As Worksheet
Dim R As Worksheet
Set R = Sheets("Front")
Set S = Sheets("CHECK LIST")

Counter2 = 17
For Counter1 = 12 To 150

    Dim SL As Range
    Dim RL As Range
    Set SL = S.Range(S.Cells(Counter1, 1), S.Cells(Counter1, 10))
    Set RL = R.Range(R.Cells(Counter2, 1), R.Cells(Counter2, 10))

    Set curCell = Worksheets("CHECK LIST").Cells(Counter1, 6)
    Set checkCell = Worksheets("Front").Cells(3, 5)
    If curCell.Value = checkCell.Value Then

        SL.Copy
        RL.PasteSpecial

        Counter2 = Counter2 + 1
    End If
Next Counter1

The PasteSpecial method has several parameters:

.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, _
            Transpose:=False

If you are not using these, then maybe you can do a "normal" copy & paste?

SL.Copy RL

Avoiding Select in VBA Excel macros with variable ranges

1 Answers