|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
你先看懂我的代码再说,OK?
- Public Function YILOUHH(rgSource As Range, Optional rgConditions As Range)
- Application.Volatile True
- Dim arrSource As Variant, arrResult As Variant
- Dim objFind As Object
-
- Dim lngRow As Long
-
- arrSource = rgSource
- ReDim arrResult(LBound(arrSource) To UBound(arrSource), 1 To 1)
- Set objFind = CreateObject("Scripting.Dictionary")
-
- ''''''''''''''''''''''''''''''''''''''''''''''''''''''
- '如果有条件,进行数据规整
- If Not (rgConditions Is Nothing) Then
- '只有一个条件单元格
- If rgConditions.Count = 1 Then
- For lngRow = LBound(arrSource) To UBound(arrSource)
- If arrSource(lngRow, 1) <> rgConditions.Value Then
- arrSource(lngRow, 1) = ""
- End If
- Next
- End If
-
- '有三个条件单元格
- If rgConditions.Count = 3 Then
- '只有第一个单元格有值,同只有一个条件单元格
- If rgConditions(1) <> "" And rgConditions(2) = "" And rgConditions(3) = "" Then
- For lngRow = LBound(arrSource) To UBound(arrSource)
- If arrSource(lngRow, 1) <> rgConditions(1).Value Then
- arrSource(lngRow, 1) = ""
- End If
- Next
- End If
-
- '前两个单元格有值,或者的关系
- If rgConditions(1) <> "" And rgConditions(2) <> "" And rgConditions(3) = "" Then
- For lngRow = LBound(arrSource) To UBound(arrSource)
- If arrSource(lngRow, 1) = rgConditions(1).Value Or arrSource(lngRow, 1) = rgConditions(2).Value Then
- arrSource(lngRow, 1) = "A"
- Else
- arrSource(lngRow, 1) = ""
- End If
- Next
- End If
-
- '三个单元格都有值,或者的关系
- If rgConditions(1) <> "" And rgConditions(2) <> "" And rgConditions(3) <> "" Then
- For lngRow = LBound(arrSource) To UBound(arrSource)
- If arrSource(lngRow, 1) = rgConditions(1).Value Or arrSource(lngRow, 1) = rgConditions(2).Value Or arrSource(lngRow, 1) = rgConditions(3).Value Then
- arrSource(lngRow, 1) = "A"
- Else
- arrSource(lngRow, 1) = ""
- End If
- Next
- End If
-
-
- '一、三单元格有值,按>=1 且 <=3 规整
- If rgConditions(1) <> "" And rgConditions(2) = "" And rgConditions(3) <> "" Then
- For lngRow = LBound(arrSource) To UBound(arrSource)
- If arrSource(lngRow, 1) >= rgConditions(1) And arrSource(lngRow, 1) <= rgConditions(3) Then
- arrSource(lngRow, 1) = "A"
- Else
- arrSource(lngRow, 1) = ""
- End If
- Next
- End If
- End If
-
- End If
-
- '数据规整完毕
- '==============================================
-
- '''''''''''''''''''''''''''''''''''''''''''''''
- '数据查找计算
-
- For lngRow = LBound(arrSource) To UBound(arrSource)
- arrResult(lngRow, 1) = ""
- If arrSource(lngRow, 1) <> "" Then
- If objFind.Exists(arrSource(lngRow, 1)) = True Then
- arrResult(lngRow, 1) = lngRow - objFind(arrSource(lngRow, 1))
- End If
- objFind(arrSource(lngRow, 1)) = lngRow
- End If
- Next
-
- Set objFind = Nothing
- YILOUHH = arrResult
-
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|