用多张纸运行一个marcon - 调用一个子 [英] Running a marcon with multiple sheets - Calling a sub
问题描述
b
我是VBA的新手,并尝试在工作簿的多个工作表上运行macron。 我有以下代码完美运行:
$
子测试()
Dim ws As Worksheet
Dim i As Integer
对于每个ws在ThisWorkbook.Worksheets
随着ws
i = 1
做什么我< 10
$
.Cells(i,12)= .Cells(i,11)
i = i + 1
循环
'调用尝试
结束与
下一个ws
结束子
$
然而,当我将Do循环行(包括i = 1)更改为带有名称的Sub时"尝试"如下:
$
Sub Try()
Dim i As Integer
&NBSP;&NBSP;&NBSP; i = 1
做什么我< 10
$
.Cells(i,11)= .Cells(i,12)
i = i + 1
循环
End Sub
和电话 "尝试"在"测试"的第一个Sub中,它给出了"无效或不合格的参考"的错误消息。 for .Cells(i,12)。 有人会帮我吗? 非常感谢!
推荐答案
With ws ... End With块在Test宏中。在该块中,您可以使用.Cells作为ws.Cells的简写。
The With ws ... End With block is in the Test macro. Within that block, you can use .Cells as shorthand for ws.Cells.
但是Try宏不会"知道"。关于这个用ws ...结束有块,所以.Cells导致错误。
But the Try macro doesn't "know" about this With ws ... End With block, so .Cells causes an error.
你可以解决这个问题如下:
You can solve this as follows:
Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Call Try(ws)
Next ws
End Sub
Sub Try(ws As Worksheet)
Dim i As Integer
i = 1
With ws
Do While i < 10
.Cells(i, 11) = .Cells(i, 12)
i = i + 1
Loop
End With
End Sub
这篇关于用多张纸运行一个marcon - 调用一个子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!