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
– chancea, Commented Jul 1, 2015 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
– Rory, Commented Jul 1, 2015 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
– BruceWayne, Commented Jul 1, 2015 at 17:13
possible duplicate of 2 ways for "ClearContents" on VBA Excel, but 1 work fine. Why? — Byron Wall
– Byron Wall, Commented Jul 1, 2015 at 18:09

ChipsLetten · Accepted Answer · 2015-07-01 21:29:29Z

1

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

answered Jul 1, 2015 at 21:29

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Byron Wall Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Avoiding Select in VBA Excel macros with variable ranges

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related