CODEWITHSUNDEEP
vbaexcel
NNOPP
NNOPP

Reputation: 107

How to assign range of cells to Range variable

My environment: MS Excel 2013

I want to assign range of cells to my range variable with code below

Dim strSourceFile As String
Dim wbSource As Workbook

strSourceFile = "D:\csv1.csv"
Set wbSource = Workbooks.Open(strSourceFile)
Set rngY = wbSource.Sheets(1).Range(Cells(2, RefCol), Cells(LastSource, RefCol))

I get error at last row while trying to assign value to rngY.

Run-time error '1004':
Application-defined or object-defined error

Upvotes: 0

Views: 2274

Answers (1)

user6432984
user6432984

Reputation:

Cells(2, RefCol) is referencing the cells on the active worksheet. You must qualify all the range objects.

Example 1:

Set rngY = wbSource.Sheets(1).Range( wbSource.Sheets(1).Cells(2, RefCol),  wbSource.Sheets(1).Cells(LastSource, RefCol))

Example 2:

With wbSource.Sheets(1)
    Set rngY = .Range( .Cells(2, RefCol), .Cells(LastSource, RefCol))
End With

Upvotes: 1

Related Questions