|
- Sub Test()
- Dim sh As Worksheet, lngRow As Long, lngCol As Long
- Dim arr As Variant, objDic As Object, strKey As String, lngItem As Long
-
- Set sh = Sheets("6688")
- lngRow = sh.Range("B" & Rows.Count).End(xlUp).Row
-
- arr = sh.Range("B3:E" & lngRow)
- Set objDic = CreateObject("Scripting.Dictionary")
-
- For lngCol = LBound(arr, 2) To UBound(arr, 2)
- objDic.RemoveAll '按列
- For lngRow = LBound(arr) To UBound(arr)
- strKey = Trim(arr(lngRow, lngCol))
- If strKey <> "" Then
- If objDic.Exists(strKey) Then
- lngItem = lngRow - objDic(strKey) - 1
- objDic(strKey) = lngRow
- arr(lngRow, lngCol) = lngItem
- Else
- objDic(strKey) = lngRow
- arr(lngRow, lngCol) = "" '首次出现
- End If
- End If
- Next
- Next
-
- sh.Range("F3").Resize(UBound(arr), UBound(arr, 2)) = arr
- Set objDic = Nothing
- MsgBox "OK"
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|