|
- Option Explicit
- Sub 统计个数()
- Dim LastRow As Long, Row As Long, R1 As Integer, R2 As Integer
- Dim Counter As Integer
- LastRow = Cells(Rows.Count, 1).End(xlUp).Row
- Range("C:D").ClearContents
- Range("A:B").Interior.Color = xlNone
-
- For Row = 2 To LastRow
- If Counter = 0 Then '计数为0时
- If Cells(Row, 2) = "×" Then '找到第一个"×"并把行号赋给R1得到开始行号
- Counter = 1 '用于统计"×"的个数
- R1 = Row
- End If
- ElseIf Cells(Row, 2) = "√" Or Row = LastRow Then '如果已到尾行或单元格为"√"
- If Counter >= 3 Then '满足条件:"×"的个数大于3
- R2 = Row
- Do '往回找到最后一个"×"并把行号赋值给R2
- R2 = R2 - 1
- Loop Until Cells(R2, 2) = "×"
- Cells(R2, 3) = Cells(R1, 1) & "-" & Cells(R2, 1)
- Range(Cells(R1, 2), Cells(R2, 2)).Interior.Color = 5296274
- End If
- Counter = 0
- ElseIf Cells(Row, 2) = "×" Then '计数不为0且单元格为"×"
- Counter = Counter + 1 '对"×"计数
- End If
- Cells(Row, 4) = Counter '此行测试用,可删除
- Next Row
- End Sub
复制代码 |
|