CODEWITHSUNDEEP
vba
Garry W
Garry W

Reputation: 311

Selecting a range using variable

The following code gives me an "Application defined or object defined error"

Sub Apply_Changes()
    Dim col_ref As Integer
    col_ref = 5
    Worksheets("Sheet1").range(Cells(10, col_ref),cells(20,col_ref)).Select
End Sub

Could someone explain?

Is there another way of defining the range?

Upvotes: 0

Views: 50

Answers (2)

Scott Craner
Scott Craner

Reputation: 152475

You need to apply the parantage to the Cells() inside the Range.:

Worksheets("Sheet1").range(Worksheets("Sheet1").Cells(10, col_ref),Worksheets("Sheet1").cells(20,col_ref)).Select

or use with to shorten:

With Worksheets("Sheet1")
    .range(.Cells(10, col_ref),.cells(20,col_ref)).Select
End with

Note I assume this is for practice, the selecting of a range to do something to it is wasted energy, simply do what you want to the range.

With Worksheets("Sheet1")
    .range(.Cells(10, col_ref),.cells(20,col_ref)).copy Worksheets("Sheet2").Range("A1")
End with

If you want to actually select the range then the sheet must be active:

With Worksheets("Sheet1")
    .Activate
    .range(.Cells(10, col_ref),.cells(20,col_ref)).Select
End with

Upvotes: 3

JNevill
JNevill

Reputation: 50034

You must qualify your Cells() otherwise you are saying "The cells in whatever worksheet is currently active", which doesn't make sense inside of Worksheeets("Sheet1").Range()

Worksheets("Sheet1").range(Worksheets("Sheet1").Cells(10, col_ref),Worksheets("Sheet1").cells(20,col_ref)).Select

Consider, instead doing this like:

Worksheets("Sheet1").Cells(10, col_ref).Resize(10).Select

Essentially resizing the single cell 10 more rows (down to 20).

Upvotes: 3

Related Questions