vba 用“Cells()”引用不同工作簿中的范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13704003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba refer to range in different workbook with "Cells()"
提问by Gijs vK
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).
遗憾的是，这会导致 1004 错误（应用程序定义或对象定义错误）。
any ideas how to solve this?
任何想法如何解决这个问题？
thanx, Gijs.
谢谢，吉斯。
回答by Max Makhrov
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.
注意点：您指的是特定工作表中的单元格，因此请在语句中的 .Cells 之前粘贴点。
回答by Trace
You don't need to activate the workbook / worksheet. Use the "Set" keyword to define a range.
您不需要激活工作簿/工作表。使用“Set”关键字定义范围。
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.
根据您想要做什么 (?)
为了比较范围，我通常定义数组并比较这些数组中的值。
