使用工作表编号而不是工作表名称的 VBA 公式

2024-05-20 • 问答

我正在使用 VBA 编写宏，它完全按照我的意愿工作，只是我希望我的公式循环遍历工作表而不是使用“SAFO-1”上的数据，“SAFO-1”指的是鱼 Salvelinus fontinalis (SAFO)。我有很多鱼种（例如，Morone saxatilis (MOSA)），如果我能参考表号而不是它们的名字，那会更实用。不幸的是，我不决定工作表名称，它们必须保持原样，因为我们正在为每个样本使用唯一名称的共享项目工作。项目之间的工作表名称更改，我希望能够在所有项目中使用我的代码。这是我当前的代码：

Sub Mean()
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim Sheet As Integer
    
    k = 4
    i = Application.Sheets.Count
    
    For Sheet = 2 To i
        Worksheets(Sheet).Select
        j = 3
            Do While activeCell.Value <> "0"
                Range("A" & j).Select
                If activeCell.Value = "0" Then
                    Range("A1").Copy
                    Worksheets("Mean").Range("A" & Sheet + 1).PasteSpecial Paste:=xlPasteValues
                    Worksheets("Mean").Range("B" & Sheet + 1).Formula = "=(('SAFO-1'!B80)-('SAFO-1'!B75))"
                    Worksheets("Mean").Range("C" & Sheet + 1).Formula = "=(('SAFO-1'!C80)-('SAFO-1'!C75))"
                    For k = 4 To 41
                        Worksheets("Mean").Cells(Sheet + 1,k).FormulaR1C1 = "=AVERAGE('SAFO-1'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"
                    Next k
                Else
                j = j + 1
                End If
            Loop
    Next Sheet
    Range("B1:AP2").Copy Worksheets("Mean").Range("A1")
    Worksheets("Mean").Select
End Sub

我的想法是用“Sheet1”替换“SAFO-1”，以便最终能够编写如下内容：

Worksheets("Mean").Cells(Sheet + 1,k).FormulaR1C1 = "=AVERAGE('Sheet "& Sheet")'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"

提前致谢！

威廉·福廷

首先，我们将停止使用 .Select，而是使用对象句柄。我不完全确定您的工作表的名称来自哪里，但我将假设它与循环有关并以此为例。我们使用它的编号 Set currentSheet = Worksheets(Sheet) 在工作表上获得一个对象句柄，然后我们可以获取它的名称并在公式 currentSheet.Name 中我们需要的地方使用它。

我希望即使此代码不是一个完整的解决方案，它也能向您展示如何到达目的地。

Option Explicit

Public Sub Mean()
    
    Dim j As Long
    Dim k As Long
    Dim Sheet As Long
    
    k = 4
    
    For Sheet = 2 To Application.Sheets.Count
        Dim currentSheet As Worksheet
        Set currentSheet = Worksheets(Sheet)
        j = 3
        
        Do
            Dim currentCell As Range
            Set currentCell = currentSheet.Range("A" & j)
            If currentCell.Value = "0" Then
                With Worksheets("Mean")
                    .Range("A" & Sheet + 1).Value = currentSheet.Range("A1").Value
                    .Range("B" & Sheet + 1).Formula = "=(('" & currentSheet.Name & "'!B80)-('" & currentSheet.Name & "'!B75))"
                    .Range("C" & Sheet + 1).Formula = "=(('" & currentSheet.Name & "'!C80)-('" & currentSheet.Name & "'!C75))"
                    For k = 4 To 41
                        .Cells(Sheet + 1,k).FormulaR1C1 = "=AVERAGE('" & currentSheet.Name & "'!R" & j + 10 & "C" & k & ":R" & j - 9 & "C" & k & ")"
                    Next k
                End With
            Else
                j = j + 1
            End If
        Loop While currentCell.Value <> "0"
    Next Sheet
    
    currentSheet.Range("B1:AP2").Copy Worksheets("Mean").Range("A1")
    Worksheets("Mean").Select
End Sub

我们可以在 VBA 中创建一组工作表名称，并使用它们来创建我们放入工作表中的公式。例如：

Sub useNumber()
    sh = Array("big worksheet name","collosal worksheet name","mammoth worksheet name","tiny worksheet name")
    Sheets("Sheet1").Range("A1").Formula = "=SUM('" & sh(1) & "'!A3:A6)"
End Sub

如果您有很多工作表，请使用 For 循环而不是 Array() 函数来填充数组。

运行它会创建：

=SUM('collosal worksheet name'!A3:A6)

在Sheet1单元格A1

这种方法使遍历数据表更容易：

Sub useNumberloop()
    sh = Array("big worksheet name","tiny worksheet name")
    
    For i = 1 To 4
        Sheets("Sheet1").Range("A" & i).Formula = "=SUM('" & sh(i - 1) & "'!A3:A6)"
    Next i
End Sub

使用工作表编号而不是工作表名称的 VBA 公式

PandaKing1988 回答：使用工作表编号而不是工作表名称的 VBA 公式

大家都在问