Excel VBA：将公式分配给同一张表中的多个动态范围表 [英] Excel VBA : assign formula to multiples dynamic range table in same sheet

问题描述

我是新的，学习Excel VBA。我现在遇到这个问题

1. 工作表中有超过10个表（表数不一致）


2. 列的数量是一致的，但不是每个表格中的行


3. 我想将一个总行应用到每个表的末尾


4. 之后，我将对每个表格应用相同的公式，并将结果放在每个表的右侧。

这可能很容易，但核心问题是范围是未知的。
- 由于它不是Excel中的实际表，所以我尝试首先通过创建表来定义数据的范围，然后再次，我不知道如何创建表，而不知道

以下是我想出的东西（这不是很动态）

  Sub plsWork（）
 
设置u = ThisWorkbook.Worksheets（Sheet2）
设置f = u.Range（A）Find（what： =Name，lookat：= xlPart）
a = f.Address 
 
设置sht = u.Range（a）
 
'试图将其插入到表的结尾
总计= Sum（u.Offset（2，1）+ u.Offset（3，1）+ u.Offset（4，1））
 
如果Cells我，2）= vbNullString然后'这已经不适用，因为colB中的前2行有空字符串
 u.Offset（i，1）.Value =总计
 
'表名在F2 
 u.Offset（-2,5）.Value = u.Offset（-3，0）.Value 
 u.Offset（-2,6）.Value =总
 
 u.Offset（-1，5）.Value = u.Offset（2，0）.Value 
 u.Offset（-1,6）.Value = Sum（u.Of fset（2，1）+ u.Offset（2，2）+ u.Offset（2,3））
 
 u.Offset（0，5）.Value = u.Offset（3， 0）.Value 
 u.Offset（0,6）.Value = Sum（u.Offset（3，1）+ u.Offset（3，2）+ u.Offset（3，3））
 
 u.Offset（1，5）.Value = u.Offset（4，0）.Value 
 u.Offset（1，6）.Value = Sum（u.Offset（4， 1）+ u.Offset（4，2）+ u.Offset（4，3））
 
 End Sub 
  

哦，当我运行上面的代码，我在SUM上收到错误Sub或Function未定义

这是表格中的表格的图像

黄色突出显示为执行子程序后将会发生什么。

在Excel表格中应用公式非常简单，并将公式粘贴到每个表格中，

但是它是乏味的，所以我尝试出来一个vba代码来帮助，以便宏可以按计划运行。

我正在抓我的头和搜索过去两天来回，
我仍然没有一个线索如何编写这个。

那么任何专家可以告诉我，如果这是可能的吗？喜欢不知道的范围？

如果是这样，你们可以给我一些关于如何实现这个的信息吗？

谢谢。我真的想知道这是否可以完成。

这是我尝试的形象使用提供的答案

解决方案

你可以尝试这样的东西...

下面的代码将为每个具有多个行和四列的表插入一个Total Row。

  Sub InsertTotalInEachTable（）
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim i As Integer，r As Long，j As Long 
 
 Application.ScreenUpdating = False 
 
设置ws = ActiveSheet 
 
对于每个rng在ws.UsedRange.SpecialCells（xlCellTypeConstants，3）.Areas 
如果rng.Rows.Count> 1和rng.Columns.Count = 4然后
j = 2 
r = rng.Cells（rng.Rows.Count，1）.Row + 1 
单元格（r，rng.Columns（1 ）.Column）.Value =Total
对于i = rng.Columns（2）.Column到rng.Columns（2）.Column + 2 
 Cells（r，i）.Formula = = SUM（& rng.Columns（j）.Address&）
j = j + 1 
 Next i 
 End If 
 Next rng 
应用程序.ScreenUpdating = True 
 End Sub 
  

I am new and learning Excel VBA. I am now having this problem

1. There is more than 10 tables in a worksheet (number of tables is not consistent)
2. The number of columns are consistent but not the rows in each tables
3. I would like to apply a total row to the end of every table
4. After that, I will apply the same formula to every table and put the results on the right side of each table

This could be easy but the core problem is that the range is unknown. - As it is not an actual table in Excel, so I tried to first define the range of the data by creating table for it, then again, I don't have idea on how to create the table without knowing the range.

Below is something I came up with (which is not very "dynamic")

Sub plsWork()

Set u = ThisWorkbook.Worksheets("Sheet2")
Set f = u.Range("A").Find(what:="Name", lookat:=xlPart)
a = f.Address

Set sht = u.Range(a)

'trying to insert this at the end of the table
Total = Sum(u.Offset(2, 1) + u.Offset(3, 1) + u.Offset(4, 1))

If Cells(i, 2) = vbNullString Then 'this is already not applicable as the top 2 row in colB has null string
u.Offset(i, 1).Value = Total

'putting the table name at F2
u.Offset(-2, 5).Value = u.Offset(-3, 0).Value
u.Offset(-2, 6).Value = Total

u.Offset(-1, 5).Value = u.Offset(2, 0).Value
u.Offset(-1, 6).Value = Sum(u.Offset(2, 1) + u.Offset(2, 2) + u.Offset(2, 3))

u.Offset(0, 5).Value = u.Offset(3, 0).Value
u.Offset(0, 6).Value = Sum(u.Offset(3, 1) + u.Offset(3, 2) + u.Offset(3, 3))

u.Offset(1, 5).Value = u.Offset(4, 0).Value
u.Offset(1, 6).Value = Sum(u.Offset(4, 1) + u.Offset(4, 2) + u.Offset(4, 3))

End Sub

Oh, and when I run above code, I got error "Sub or Function not defined" on "SUM"

Here is the image of the tables in a sheet
yellow highlighted is what going to be there after executing the sub.

It was quite easy applying formula in Excel sheet and copy paste the formula to each tables,
but it was tedious, so I try to come out with a vba code to help so that the macro could run based on schedule.

I'm scratching my head and searching to and fro for the past two days, I still haven't got a clue on how to code this.
So can any expert tell me if this is possible? like without knowing the range?
If so, could you guys shed me with some info on how to achieve this?
Thank you. I really want to know if this can be done or not.

Here is an image of my attempt using provided answer

解决方案

You may try something like this...

The code below will insert a Total Row for each table which has more than one row and four columns in it.

Sub InsertTotalInEachTable()
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer, r As Long, j 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
        j = 2
        r = rng.Cells(rng.Rows.Count, 1).Row + 1
        Cells(r, rng.Columns(1).Column).Value = "Total"
        For i = rng.Columns(2).Column To rng.Columns(2).Column + 2
            Cells(r, i).Formula = "=SUM(" & rng.Columns(j).Address & ")"
            j = j + 1
        Next i
    End If
Next rng
Application.ScreenUpdating = True
End Sub

这篇关于Excel VBA：将公式分配给同一张表中的多个动态范围表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！