CODEWITHSUNDEEP
vbarange
Gijs vK
Gijs vK

Reputation: 21

vba refer to range in different workbook with "Cells()"

i want to refer to a range in a different book, that is defined from this works:

Workbooks(macrofilename).Sheets(IDsheetname).Activate
lookRange = Range(Cells(startrow, startcol), Cells(rows, cols))
colno = Application.Match(myName, lookRange, 0)

but i don't want to activate the other sheet so i tried it to add the workbook&sheet to the range definition: 

lookRange = Workbooks(filename).Sheets(sheetname).Range(Cells(startrow, startcol), Sheets(Cells(rows, cols))

sadly, this results in in a 1004 error (application-defined or object-defined error).

any ideas how to solve this?

thanx, Gijs.

Upvotes: 0

Views: 35980

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 21

You can try first make reference to worksheet:

Dim Ws As Worksheet
Set Ws =  Workbooks(filename).Sheets(sheetname)

And then make reference to range:

With Ws
    Set rLookRange = Range(.Cells(startrow, startcol), .Cells(rows, cols))
End With

Pay attention on dots: you refer to Cells in specific Worksheet, so paste dots before .Cells in your statement.

Upvotes: 2

Trace
Trace

Reputation: 18899

You don't need to activate the workbook / worksheet. Use the "Set" keyword to define a range.

The syntax would be something like: 

dim rLookRange as Range


set rLookRange = Workbooks(filename).Sheets(sheetname).Range(Cells(startrow, startcol), Cells(rows, cols))

Depending on what you want to do (?)
For comparing ranges, I usually define arrays and compare the values inside these arrays.

Upvotes: 0

Related Questions