Excel VBA：将计算添加到同一张表中的每个表 [英] Excel VBA : add calculation to every tables in same sheet

问题描述

这是这里。

所需产出

表

感谢@sktneer谁帮我解决问题的第一部分，现在我想要

1. 调整代码，因为我在表名和
   表之间添加了几行（行），添加列也是


2. 添加表格右侧的公式与表名称

我已经在代码中添加了一些假设（作为注释）以前的答案是尝试失败的图像。

如果是正确的，请你让我知道吗？

因为我不完全明白代码是如何工作的谷歌搜索

解决方案

请尝试以下代码。
下面的代码将不会在进行总计摘要时插入类的名称。
但代码将为您在所需输出中显示的每个表创建一个摘要权限，前提是在表上每个表的右侧有足够的空间用于汇总，否则代码将覆盖任何数据。

  Sub InsertSummaryForEachTable（）
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim i As Integer，c As Long 
 
 Application.ScreenUpdating = False 
 
设置ws = ActiveSheet 
 
对于每个rng在ws.UsedRange.SpecialCells（xlCellTypeConstants ，3）.Areas 
如果rng.Rows.Count> 1和rng.Columns.Count = 4然后
c = rng.Cells（1，rng.Columns.Count）.Column + 2 
单元格（rng.Rows（1）.Row，c）.Value =Total
 For i = 2 To rng.Rows.Count 
 rng.Rows（i）.Cells（1）.Select 
 Cells（rng.Rows（i）.Row， c）= rng.Rows（i）.Cells（1）
 Cells（rng.Rows（i）.Row，c + 1）== SUM（& rng.Rows（i）.Address& ;
 Next i 
 End If 
 Next rng 
 Application.ScreenUpdating = True 
 End Sub 
  pre> 
this is a update version of here.

Desired output  

Table

Thanks @sktneer who helped me out solving the first part of problem, now I want to 

adjust on the code as I've added a few lines(rows) between table name and
table, with added columns too   
add the formula to the right side of the table with table name  
I have included some of my assumptions on the code (as comments) from previous answer in "failed attempt" image.

Would you guys please let me know if it's correct?

Because I don't fully understand how did the code works even though I did googled on the statements.
解决方案
Try the below code.
The code below will not insert the name of the class while making the Total summary.
But the code will create a summary right to each table as you showed in your desired output, provided there is enough space for summary to the right of each table on the sheet otherwise the code will overwrite any data if found there.
Sub InsertSummaryForEachTable()
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer, c As Long

Application.ScreenUpdating = False

Set ws = ActiveSheet

For Each rng In ws.UsedRange.SpecialCells(xlCellTypeConstants, 3).Areas
    If rng.Rows.Count > 1 And rng.Columns.Count = 4 Then
        c = rng.Cells(1, rng.Columns.Count).Column + 2
        Cells(rng.Rows(1).Row, c).Value = "Total"
        For i = 2 To rng.Rows.Count
            rng.Rows(i).Cells(1).Select
            Cells(rng.Rows(i).Row, c) = rng.Rows(i).Cells(1)
            Cells(rng.Rows(i).Row, c + 1) = "=SUM(" & rng.Rows(i).Address & ")"
        Next i
    End If
Next rng
Application.ScreenUpdating = True
End Sub


                        
这篇关于Excel VBA：将计算添加到同一张表中的每个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！