vba 从不同的工作表和 vlookup 功能设置范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15878395/
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
Set a range from different worksheet and vlookup function
提问by Whisnuraga Putra
This code is working properly:
此代码工作正常：
Sub test_range()
Dim Range1 As Range
Set Range1 = Sheet4.Range("B2:F6081")
End Sub
When I change the range into variable it gives me error 1004:
当我将范围更改为变量时，它给了我错误 1004：
Sub test_range()
Dim Range1 As Range
Set Range1 = Sheet4.Range(Cells(2, 2), Cells(6081, 6))
End Sub
What is wrong with my "selecting cells" conversion?
我的“选择单元格”转换有什么问题？
Furthermore, I would like to apply it onto vlookup function:
此外，我想将其应用于 vlookup 函数：
For C = 2 To ColNumber
C4 = C + 4
For R = 2 To RowNumber
Set Condition = Sheet1.Cells(R, 2)
'MsgBox (Condition)
Set Range1 = Sheet4.Cells(2, 2).Offset(6079, C + 3) 'B2 (row 2 column 2) : F6081 (row 6081 column 6)
'MsgBox (Range1)
arg1 = 5
arg2 = False
Sheet1.Cells(R, C4).Value = Application.WorksheetFunction.VLookup(Condition, Range1, 5, 0)
Next R
Next C
I tried to modify range into
我试图将范围修改为
Range1 = Sheet4.Cells(2, 2).Offset(6079, C + 3)
The range function worked, but the vlookup function (again) gave me error 1004
range 函数有效，但 vlookup 函数（再次）给了我错误 1004
I read some cases for vlookup and every one dim result of vlookup as a variant. Should I do that also? Can't I just put the result of it onto a value of a column?
我阅读了一些 vlookup 的案例，以及 vlookup 的每一个暗淡结果作为变体。我也应该这样做吗？我不能把它的结果放到一个列的值上吗？
回答by Peter Albert
Cellsmeans ActiveSheet.Cells! Therefore, if your active sheet is not Sheet4, Sheet4.Range(Cells(...will give you an error!
Cells意味着ActiveSheet.Cells！所以，如果你的active sheet不是Sheet4，Sheet4.Range(Cells(...会给你报错！
Use Sheet4.Range(Sheet4.Cellsinstead.
使用Sheet4.Range(Sheet4.Cells来代替。
Or even better, use Sheet4.Cells(2,2).Resize(6080,5)
或者甚至更好，使用 Sheet4.Cells(2,2).Resize(6080,5)
