VBA读取工作表和单元格编号并执行功能

Question

我在excel中有三列，第一列包含工作表的名称，第二列包含单元格编号，第三列包含一个类似=SUM(A1:A10)的单元格中编写的函数，即SUM (A1:A10)。

我希望VBA从单元格中读取工作表的名称，转到提到的单元格，然后执行该函数。

      A          B            C
1  Sheet2       B3      =SUM(A1:A10)

所以我希望在Sheet2单元格B3上执行SUM(A1:A10)，大约有100行，所以代码将读取到Used.Range或Column.End

Answer 1

以下内容应该会有所帮助

Sub Demo()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim strSheet As String, strCell As String, strFormula As String
    Set ws = ThisWorkbook.Sheets("Sheet1")  'change Sheet1 to your data sheet

    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row    'get last row with data using Column A
        For i = 2 To lastRow                'loopthrough all the rows
            strSheet = .Range("A" & i)      'this is sheet name from colunm A
            strCell = .Range("B" & i)       'this is cell address from colunm B
            strFormula = .Range("C" & i)    'this is formula from colunm C

            ThisWorkbook.Sheets(strSheet).Range(strCell).Formula = strFormula
            'remove formula and keep only values
            ThisWorkbook.Sheets(strSheet).Range(strCell).Value = ThisWorkbook.Sheets(strSheet).Range(strCell).Value
        Next i
    End With
End Sub

Answer 2

我建议您阅读答案，并与您自己的情况协调。

抛出评论。

给你..。

Sub SM123()

    'Calculates used range or column end of destination Sheet that mentioned in A1 cell, So revise the range of used in C1 formula.
    Dim SH As Integer
    SH = Worksheets(ActiveSheet.Range("A1").Value).Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.Range("C1").Formula = "=SUM(A1:A" & SH & ")"

    'Perform your thinking formula.        
    Worksheets(ActiveSheet.Range("A1").Value).Range(ActiveSheet.Range("B1").Value).Formula = _
        ActiveSheet.Range("C1").Formula

End Sub