|
left369 发表于 2014-5-31 16:50
你好,老师。我没表述清楚哦。不好意思。再做了几个例子。老师看看。
楼主的要求是最大、最小值在同一列内不能重复。再修改一下代码:- Option Explicit
- Sub test()
- Dim arr_col, arr_row, brr, crr(), iMax%, iMin%
- Dim i%, j%, k%, x%, RngMax As Range, RngMin As Range
- With Sheets(1)
- arr_row = .Range("a17:a26")
- arr_col = .Range("b13", Cells(13, Cells(13, Columns.Count).End(1).Column))
- brr = .Range("b17").Resize(UBound(arr_row), UBound(arr_col, 2))
- ReDim crr(1 To 2, 1 To UBound(arr_col, 2))
- For i = 1 To UBound(arr_row)
- iMax = 0: iMin = 99
- For j = 1 To UBound(arr_col, 2)
- If arr_col(1, j) = arr_row(i, 1) Then
- If Len(brr(i, j)) > 0 Then
- If brr(i, j) = WorksheetFunction.Max(WorksheetFunction.Index(brr, 0, j)) Then
- k = 0
- For x = 1 To UBound(arr_row)
- If brr(x, j) = brr(i, j) Then k = k + 1
- Next
- If k = 1 Then crr(1, j) = i
- End If
- If brr(i, j) = WorksheetFunction.Min(WorksheetFunction.Index(brr, 0, j)) Then
- k = 0
- For x = 1 To UBound(arr_row)
- If brr(x, j) = brr(i, j) Then k = k + 1
- Next
- If k = 1 Then crr(2, j) = i
- End If
- End If
- End If
- Next
- Next
- For j = 1 To UBound(crr, 2)
- If crr(1, j) > 0 Then
- If RngMax Is Nothing Then
- Set RngMax = .Cells(crr(1, j) + 16, j + 1)
- Else
- Set RngMax = Union(RngMax, .Cells(crr(1, j) + 16, j + 1))
- End If
- End If
- If crr(2, j) > 0 Then
- If RngMin Is Nothing Then
- Set RngMin = .Cells(crr(2, j) + 16, j + 1)
- Else
- Set RngMin = Union(RngMin, .Cells(crr(2, j) + 16, j + 1))
- End If
- End If
- Next
- End With
- If Not RngMax Is Nothing Then RngMax.Interior.ColorIndex = 3
- If Not RngMin Is Nothing Then RngMin.Interior.ColorIndex = 41
- End Sub
复制代码 |
|