循环遍历表并基于动态表引用执行计算

Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:

是否有可能循环遍历一个表列表并执行一些计算，这些计算具有来自不同工作表列表的表引用?例如，您有一个工作表的列表(列表1)，它将是计算的目的地，您还有第二个工作表的列表(列表2)，计算是基于这些工作表的。那么，我们假设列表1中有sheet1和sheet2，在列表2中有sheetA和sheetB。基于sheetA的计算将出现在sheet1中，基于sheetB的计算将出现在sheet2中。我想代码应该是这样的:

Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")
For Each sheet_name In Sheets("WS").Range("C:C")
    If sheet_name.Value = "" Then
        Exit For
    Else
        With Sheets(sheet_name.Value)
                .Range("K1") = .Range("sheet_name2.Value!A14").Value
        End With
    End If
    Next sheet_name
End Sub

I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line: .Range("K1") = .Range("sheet_name2.Value!A14").Value

我在这行得到“运行时错误'1004:应用程序定义的或对象定义的错误”:.Range(“K1”)= .Range(“sheet_name2.Value!A14”).Value

Any help is much appreciated.

非常感谢您的帮助。

Regards,

问候,

1 个解决方案

#1

Yes, it is possible but your code is wrong.

是的，这是可能的，但是你的代码是错误的。

You will have to iterate somehow over both columns C and F, and then retrieve the matching sheet names:

您将不得不在C和F列上以某种方式进行迭代，然后检索匹配的表名:

Sub LoopthroughWorksheets()
    Dim sheet_name As Range
    Dim sheet_name2 As Range
    Set sheet_name2 = Sheets("WS").Range("F:F")
    ' NEW
    Dim counter As Long
    counter = 1

    For Each sheet_name In Sheets("WS").Range("C:C")
        If sheet_name.Value = "" Then
            Exit For
        Else
            With Sheets(sheet_name.Value)
                .Range("K1").Value = Sheets(sheet_name2(counter, 1).Value).Range("A14").Value
                ' NEW                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                   
                counter = counter + 1
            End With
        End If
    Next sheet_name
End Sub

#1