我一直在研究如何避免在Excel VBA宏中使用Select的一些想法。我的测试工作簿中有3张纸,并且想要在执行其他操作之后将光标移动到每张纸上的特定单元格。以下输入单元格值,但仅将活动单元格移动到初始工作表上的M5:
Dim rngTest As Range
Dim shtTest As Worksheet
Set rngTest = [M5]
For Each shtTest In ThisWorkbook.Sheets
With shtTest
.Range("m10").Value = "hello"
End With
rngTest.Select
Next shtTest
这是我想要做什么的地方(输入单元格值,然后在每张纸上移动到M5):
Dim rngTest As Range
Dim shtTest As Worksheet
Set rngTest = [M5]
For Each shtTest In ThisWorkbook.Sheets
With shtTest
.Range("m10").Value = "hello"
End With
[M5].Select
Next shtTest
在最上面的示例中,我尝试先使每个工作表处于活动状态,然后再移至rngTest,但这会产生1004错误。
那么,为什么[M5] .select起作用而rngTest.Select失败?
只是为了澄清:
Dim rngTest As Range
Dim shtTest As Worksheet
For Each shtTest In ThisWorkbook.Sheets
With shtTest
.Range("m10").Value = "hello"
End With
shtTest.Activate
Set rngTest = [M5]
rngTest.Select
Next shtTest
但是,我建议不要使用它[M5]
来指代特定的单元格/范围。我也更愿意使用Range("M5")
orCells(5, "M")
或Cells(5, 13)
替代。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句