|
发表于 2014-1-16 19:35
|
显示全部楼层
本楼为最佳答案
附件请测试- Private Sub CommandButton1_Click()
- Dim arr, arr1(1 To 4, 1 To 2), i&, j&, r&, c&, YGMax&, YRMax&, GRMax&, YGRMax&
- [z18].Resize(4, 2).ClearContents
- r = [d3].CurrentRegion.Rows.Count
- c = [d3].CurrentRegion.Columns.Count
- ReDim arr(1 To c)
- For j = 1 To c
- For i = 1 To r
- If Cells(i + 2, j + 3).Interior.ColorIndex = 6 Then arr(j) = arr(j) & "Y"
- If Cells(i + 2, j + 3).Interior.ColorIndex = 3 Then arr(j) = arr(j) & "R"
- If Cells(i + 2, j + 3).Interior.ColorIndex = 10 Then arr(j) = arr(j) & "G"
- Next i
- Next j
- For j = 1 To c
- If InStr(arr(j), "Y") > 0 And InStr(arr(j), "G") > 0 Then arr1(1, 1) = arr1(1, 1) + 1: arr1(1, 2) = Application.Max(arr1(1, 2), j - YGMax - 1): YGMax = j
- If InStr(arr(j), "Y") > 0 And InStr(arr(j), "R") > 0 Then arr1(2, 1) = arr1(2, 1) + 1: arr1(2, 2) = Application.Max(arr1(2, 2), j - YRMax - 1): YRMax = j
- If InStr(arr(j), "G") > 0 And InStr(arr(j), "R") > 0 Then arr1(3, 1) = arr1(3, 1) + 1: arr1(3, 2) = Application.Max(arr1(3, 2), j - GRMax - 1): GRMax = j
- If InStr(arr(j), "Y") > 0 And InStr(arr(j), "G") > 0 And InStr(arr(j), "R") > 0 Then arr1(4, 1) = arr1(4, 1) + 1: arr1(4, 2) = Application.Max(arr1(4, 2), j - YGRMax - 1): YGRMax = j
- Next j
- [z18].Resize(4, 2) = arr1
- End Sub
复制代码 |
|