作者:乔山办公网日期:
返回目录:excel表格制作
这个可以用 规划求解,1对应的单元格,就是结果
dim a as double
a = application.worksheetfunction.sum(range("a1:c10"))
'执行结果,a等于百 表格度 a1:c10 返回的数值问求和答
a = application.worksheetfunction.sum(sheets("表格名字").usedrange)
'执行结果,a等于 该表格 所有有使用的版区域权的数值求和
Sub s()
For i = 11 To 29 Step 2
c = 0
sm = 0
For j = 7 To 16
If Cells(j, i) <> "" Then
c = c + 1
sm = sm + Cells(j, i)
End If
Next
If c Then
Cells(3, i - 1) = c
Cells(5, i - 1) = sm
End If
Next
End Sub
选中一个灰色百的单元格,同时按Alt和F11进入宏界面,点菜单的插入,模度块,粘贴如问下代码:
Sub aaa()
MsgBox Selection.Interior.ColorIndex
End Sub
直接按F5运行此宏答,得到专一数字,这是灰色的VBA代码,记住它。再粘贴如下代属码:
Sub test()
Dim xRng, cell As Range
Dim xSum, xCount As Single
Set xRng = Cells.Find(Date)
If xRng Is Nothing Then
MsgBox "Not find today"
Else
Set xRng = xRng.Offset(2, 0).Resize(19, 8)
For Each cell In xRng
If cell.Interior.ColorIndex = 16 Then
xSum = xSum + cell
xCount = xCount + 1
End If
Next
End If
MsgBox xSum & Chr(10) & xCount
End Sub
把粗体的16改为刚才那个数字,按F5运行此宏,OK。