如何用VBA表格的和

870106522

Sub heji()
    Dim mRow, mCol, nRow, nCol, mSum
    Dim rg As Range
    Dim rgA As Range
   
    Set rg = Sheet1.UsedRange.CurrentRegion
    mRow = rg.Cells(rg.Count).Row
    mCol = rg.Cells(rg.Count).Column
    Set rgA = rg.Find("应发工资", lookat:=xlPart)
    nRow = rgA.Row + 1
    nCol = rgA.Column
    For i = nCol To mCol
        mSum = 0
        For j = nRow To mRow
            mSum = mSum + Cells(j, i)
        Next j
        Cells(mRow + 1, i) = mSum
    Next i
End Sub
借鉴贴友的 但老是出错

shiruiqiang

   Set rg = Sheets(1).UsedRange.CurrentRegion,导致 rg.Cells(rg.Count).Row=5,rg.Cells(rg.Count).Column=7,换成 Set rg = Sheets(1).UsedRange，分别为17和9

tanglf188

没找到你要找的字符，是不是要求合计？
Sub heji()
    Dim mRow, mCol, nRow, nCol, mSum
    Dim rg As Range
    Dim rgA As Range
    Set rg = Sheet1.UsedRange.CurrentRegion
    mRow = rg.Cells(rg.Count).Row
    mCol = rg.Cells(rg.Count).Column
    Set rgA = rg.Find("应发工资", lookat:=xlPart)
    If rgA Is Nothing Then
        MsgBox "没找到" & """应发工资"""
        Exit Sub
    End If
    nRow = rgA.Row + 1
    nCol = rgA.Column
    For i = nCol To mCol
        mSum = 0
        For j = nRow To mRow
            mSum = mSum + Cells(j, i)
        Next j
        Cells(mRow + 1, i) = mSum
    Next i
End Sub

tanglf188

shiruiqiang 发表于 2023-1-13 17:30
Set rg = Sheets(1).UsedRange.CurrentRegion,导致 rg.Cells(rg.Count).Row=5,rg.Cells(rg.Count).Colum ...

确实是这样，代码过滤出了数据区，而又在数据区查找标题字段。

tanglf188

1. Sub heji()
   
2.     Set brow = Sheet1.Range("A:B").Find("姓名", lookat:=xlPart)
   
3.     Set erow = Sheet1.Range("A:B").Find("合计", lookat:=xlPart)
   
4.     If brow Is Nothing Then Exit Sub
   
5.     If erow Is Nothing Then Exit Sub
   
6.     If erow.Row - brow.Row < 2 Then Exit Sub
   
7.     For i = 67 To 70
   
8.         Sheet1.Cells(erow.Row, i - 64) = Application.Sum(Range(Chr(i) & brow.Row + 1 & ":" & Chr(i) & erow.Row - 1))
   
9.     Next
   
10. End Sub
    
11. 
    

复制代码

870106522

感想各位帮助 问题解决了 确实是过滤出了数据区