在工作簿中的多个工作表上运行VBA代码
问题描述:
我使用下面的代码利用工作表“上周的应用程序”进行数据收集并更新同一工作表中的计数。在工作簿中的多个工作表上运行VBA代码
我已经创建了一个按钮,我试图通过按钮来运行此代码。
在执行我得到的错误行“对象需要”
[W5] = wf.CountIf(.Range("I:I"), "Trophy")
我的代码:
Sub Prevcount()
Sheets("Previous Week apps")
With ActiveWorkbook.Worksheets("Previous week apps")
[W5] = wf.CountIf(.Range("I:I"), "Trophy")
End With
With ActiveWorkbook.Worksheets("Previous week apps")
[W7] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("E:E"), "COMPATIBLE")
End With
With ActiveWorkbook.Worksheets("Previous week apps")
[W9] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("F:F"), "COMPATIBLE")
End With
With ActiveWorkbook.Worksheets("Previous week apps")
[W11] = wf.CountIfs(.Range("I:I"), "Trophy", .Range("Q:Q"), "UG")
End With
End With
End Sub
答
尝试短版(更换ActiveWorkbook
与ThisWorkbook
,你有你的代码) :
Sub Prevcount()
With ThisWorkbook.Worksheets("Previous week apps")
.Range("W5").Value = WorksheetFunction.CountIf(.Range("I:I"), "Trophy")
.Range("W7").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("E:E"), "COMPATIBLE")
.Range("W9").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("F:F"), "COMPATIBLE")
.Range("W11").Value = WorksheetFunction.CountIfs(.Range("I:I"), "Trophy", .Range("Q:Q"), "UG")
End With
End Sub
+0
在'.Range(“W5”)中获取对象所需的错误值= wf.CountIf(.Range(“I:I”),“Trophy”)' –
您是否打算将'Sheets(“Previous Week apps”)''With Sheets(“Previous Week apps “)(与代码末尾的End With匹配),如果是这样,那么你在用With代码块做什么 - 你的代码中没有任何东西正在使用它。 – YowE3K
@ YowE3K所以我应该改变它'与表(“上周应用程序”)? –