VB Excel,计算相同颜色的形状

VB Excel,计算相同颜色的形状

如何获取 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以查看还会出现哪些其他错误。

相关内容