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, Jul 01 '15 at 15:51
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, Jul 01 '15 at 15:53
@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, Jul 01 '15 at 17:13
possible duplicate of [2 ways for "ClearContents" on VBA Excel, but 1 work fine. Why?](http://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why) — Byron Wall, Jul 01 '15 at 18:09
@ChipsLetten, this question has multiple sets of errors. It's definitely a duplicate w/ the unqualified reference to `Cells`. After that, the code won't run with the improper usage of `With S` and `.SL` and `.RL` which don't exist under that object. This is also pointed out by chancea in the second comment. I suspect misusing a variable like that is a duplicate somewhere. I'll see if I can find one. Regardless, this isn't a great question because the issue on `With` is a `Compile error` and OP is reporting a runtime `Application error` which would come from `Range`. — Byron Wall, Jul 01 '15 at 22:34

score 1 · Answer 1 · answered Jul 01 '15 at 21: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

It's worth noting that this is not about Excel "knowing" which `Worksheet` contains the `Range`. The problem is that a `Worksheet` like `S` or `R` does not contain an object called `SL` or `RL`. Even if `SL` and `RL` were not defined variables, this code would still error because Excel knows those objects don't exist for a `Worksheet`. It throws the compile time error since it knows before running that this is not valid code. — Byron Wall, Jul 01 '15 at 22:38
I agree with your other changes and the good practice of using `Set` outside the loop to avoid resetting those variables constantly when they don't change. It's also possible to pull `RL` out of the `Counter1` loop. The simplified usage of `Copy` is also a good idea. — Byron Wall, Jul 01 '15 at 22:40

Avoiding Select in VBA Excel macros with variable ranges

1 Answers1