excel - 为每个打开的工作簿执行功能

Question

我正在创建一个带有命令按钮的空白工作簿，单击该按钮后，我希望它对当前打开的每个打开的工作簿执行操作（因为我将拥有其他非空白的工作簿，我希望它对其执行操作）。

运行时出现下标超出范围错误：

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    ' Activate the current workbook
    w.Activate

    ' Find the comments column (K12 should be the "Comments" column)
    If Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then

        ' Then we loop through all cells in the specified range (anything below the header row)
        Dim rng As Range, cell As Range

        ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
        Set rng = Range("A1:A500")

        ' No loop to change all comments
        For Each cell In rng
.......................

...在“ If Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then " 行。所以我认为它是从空白工作簿开始而不是找到名为“FIRE EXT.”的工作表，所以首先测试激活的工作簿是否首先具有该工作表名称的最佳实践是什么，否则继续下一个工作簿? 谢谢！

更新

这对我有用，但其他反应也会奏效。感谢大家！

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    ' Don't work on the current/blank workbook
    If w.FullName <> ThisWorkbook.FullName Then

    ' Find the comments column (K12 should be the "Comments" column)
    If w.Sheets("FIRE EXT.").Range("K12").Value = "Comments" Then

        ' Then we loop through all cells in the specified range (anything below the header row)
        Dim rng As Range, cell As Range

        ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
        Set rng = w.Worksheets("FIRE EXT.").Range("A13:A500")

        ' No loop to change all comments
        For Each cell In rng

标签： excelvbaexcel-2016

Answer 1

您需要完全限定您的所有参考资料。您还可以进行检查以确保它跳过空白工作簿（请参阅我在此更新代码中的评论）：

Sub Button1_Click()
'
' Button1_Click Macro
' Fire Ext. Comments
'
'
Dim w As Workbook

' For every open workbook...
For Each w In Application.Workbooks

    If w.FullName <> ThisWorkbook.FullName Then     '<-- TA: Add check to verify you're not working on the blank workbook

        'TA: I removed the .Activate line, that is never necessary.  Instead, fully qualify all your references

        ' Find the comments column (K12 should be the "Comments" column)
        If w.Worksheets("FIRE EXT.").Range("K12").Value = "Comments" Then   '<-- TA: Note that Worksheets is now qualified to w so that it is checking worksheets in the current w workbook

            ' Then we loop through all cells in the specified range (anything below the header row)
            Dim rng As Range, cell As Range

            ' I'm using a range of 500 to look for values, so if a file has more than 500 rows, you'll have to look at it manually
            Set rng = w.Worksheets("FIRE EXT.").Range("A1:A500")    '<-- TA: Note that Range is now qualified to w.Worksheets("FIRE EXT.") (if that isn't the correct sheet name, change this to the correct sheet name)

            ' Now loop to change all comments
            For Each cell In rng
.......................