excel - 表中如何直接用VBA计算

Question

我正在尝试直接在 Excel 中计算。

步骤： - 从 Table1（日期）中创建没有重复的新表 - 从完成中的值中获取标题 - 如果日期和名称相等，则求和

这是一个带有公式的解决方案示例。

我知道解决这个问题很热，cFormula但我无法直接使用 vba 找到快速且“易于编码”的解决方案。

Dim sbl As ListObject: Set sbl = ActiveWorkbook.Sheets(4).ListObjects("Tab_1") 'source table
Dim tbl As ListObject: Set tbl = ActiveWorkbook.Sheets(4).ListObjects("Tab_2") 'target table


MsgBox Application.WorksheetFunction.SumIfs(sbl.ListColumns(2).DataBodyRange, sbl.ListColumns(2).DataBodyRange)


End Sub

我得到了他的。但我找不到 otu 如何在“彼得”专栏中写作

有人可以帮助我或给我一个信息。

标签： excelvba

Answer 1

我找到了解决方案，希望它适合您的需求！

Sub AddFormulaAndCopyToValues2()
    Dim ws As Worksheet: Set ws = ActiveWorkbook.Sheets(1)
    Dim sbl As ListObject: Set sbl = ws.ListObjects("Tab_1")    'source table
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Tab_2")    'target table
    
    Dim tblRow As Integer: tblRow = tbl.Range.Row               ' first row
    Dim tblCol As Integer: tblCol = tbl.Range.Column            ' first Column
    
    Dim dateRng As Range: Set dateRng = sbl.ListColumns("Date").DataBodyRange
    Dim doneRng As Range: Set doneRng = sbl.ListColumns("Done").DataBodyRange
    
    ' set rng to insert formula
    Dim frmRng As Range: Set frmRng = tbl.ListColumns(2).DataBodyRange.Resize(tbl.ListRows.Count, 3)
        
    ' set rng to insert formula
    Dim rCell As Range: Set rCell = tbl.HeaderRowRange(2, 2)
    
    ' Do calculations
    Dim dateVal As Variant
    Dim nameVal As String
    For Each rCell In frmRng
        dateVal = Cells(rCell.Row, tblCol).Value
        nameVal = Cells(tblRow, rCell.Column).Value
        rCell.Formula2R1C1 = Application.WorksheetFunction.CountIfs(dateRng, dateVal, doneRng, nameVal)
    Next rCell
End Sub