vba中的.End(xlDown)替代 [英] .End(xlDown) Alternative in vba

问题描述

我已经意识到，如果您尝试从其他工作表中获取，则.End(xldirection)似乎不起作用.例如，如果您执行代码

I have realized that .End(xldirection) doesn't seem to work if you are trying to obtain from a different sheet. For example if you execute the code

Set A = Sheets("3rd sheet").Range(Cells(2, 2), Cells(2, 2).End(xlDown))

当您实际上不在第三页时，您会收到一条错误消息，指出应用程序定义或对象定义的错误.

while you are not at actually at 3rd sheet, you get an error message saying application defined or object defined error.

我确定我不是第一个处理此问题的人.有人知道如何处理此问题而不必来回切换纸张吗?

I am sure I am not the first one to deal with this problem. Anybody know how to handle this without having to switch back and forth sheets?

推荐答案

问题不是.End()，而是默认工作表.

The problem is not the .End(), is the default sheet.

您的代码应为:

Set A = Sheets("3rd sheet").Range(Sheets("3rd sheet").Cells(2, 2), Sheets("3rd sheet").Cells(2, 2).End(xlDown))

或者(如Cor_Blimey的评论所建议):

Or (as suggested by Cor_Blimey's comment):

With Sheets("3rd sheet")
    Set A = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
End With

或者(我最喜欢):

Set Sh = Sheets("3rd sheet")
Set A = Sh.Range(Sh.Cells(2, 2), Sh.Cells(2, 2).End(xlDown))

这篇关于vba中的.End(xlDown)替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！