Excel VBA : assign formula to multiples dynamic ra

问题:

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

回答1:

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