|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub Test()
- Dim sh As Worksheet, arrData As Variant, lngID As Long
- Dim shName As String, strFieldName As String, shResult As Worksheet
- Dim lngCol As Long, arrSum As Variant, arrResult As Variant
- Dim dblSum As Double
-
- shName = "汇总"
- strFieldName = "营业收入"
- Set shResult = Sheets(shName)
-
- lngID = Sheets.Count - 1
- ReDim arrResult(1 To lngID, 1 To 2)
- lngID = 1
-
- For Each sh In Sheets
- If sh.Name <> shName Then
- arrData = sh.UsedRange
- arrResult(lngID, 1) = sh.Name
- For lngCol = LBound(arrData, 2) To UBound(arrData, 2)
- If arrData(1, lngCol) = strFieldName Then
- arrSum = Application.WorksheetFunction.Index(arrData, 0, lngCol)
- dblSum = Application.WorksheetFunction.Sum(arrSum)
- arrResult(lngID, 2) = dblSum
- Exit For
- End If
- Next
- lngID = lngID + 1
- End If
- Next
-
-
- shResult.Range("A1").Resize(UBound(arrResult), 2) = arrResult
- MsgBox "OK"
- End Sub
复制代码 |
|