如何获取 Excel 表中相同颜色形状的总数?例如,我有 3 个红色形状、2 个绿色形状和 1 个黄色形状,我希望得到的结果为单元格 (1,1):3 (红色) 单元格 (2,1):2 (绿色) 单元格 (3,1):1 (黄色)
*P/s:我已经指定了一个宏来插入具有指定颜色(vbRed,vbYellow)的形状。
尝试了以下编码,没有错误消息但显示第 n 条。
Sub CalcShape()
Dim sh As Sheet1
Dim a As Single
Dim b As Single
Dim shp As Shape
Dim vbYellow As Long
Dim vbGrey As Long
Dim vbRed As Long
Dim vbGreen As Long
For Each sh In ThisWorkbook.Sheets
For Each shp In sh.Shapes
If shp.Type = ShapeType Then
If Shapes.Fill.ForeColor = vbRed Then
Sheet1.Cells(2, 1) = Shapes.Count
End If
End If
Next
For Each shp In sh.Shapes
If shp.Type = ShapeType Then
If Shapes.Fill.ForeColor = vbGreen Then
b = a + 1
Sheet1.Cells(3, 1) = b
End If
End If
Next
Next
End Sub
答案1
你没有计算你的shp
:
For Each shp In sh.Shapes
If shp.Type = ShapeType Then
If Shapes.Fill.ForeColor = vbGreen Then
b = a + 1
Sheet1.Cells(3, 1) = b
End If
End If
Next
这里:
If Shapes.Fill.ForeColor
应该
If shp.Fill.ForeColor
另一方面,您可能想考虑使用Select Case
。还可以打开Option Explicit
以查看还会出现哪些其他错误。