|
- Option Explicit
- Sub Test()
- Dim sh As Worksheet, arrData As Variant, arrResult As Variant, lngRow As Long
- Dim rgData As Range, rgSortA As Range, rgSortB As Range
- Dim strGantryID As String, strPreGantryID As String '门架编号
- Dim strCarID As String, strPreCarID As String '车牌号码
- Dim dateTradeTime As Date '交易时间
- Dim datePreTime As Date '前一个交易时间
- Dim dblMoney As Double '交易金额
- Dim strState As String '交易状态
- Dim lngAllowableDiff As Long '容许的时间差
- Dim lngDiff As Long '时间差
-
- Set sh = Sheets("原始数据") ' 操作的表名
- '排序
- Set rgData = sh.UsedRange '排序区域,全表
- Set rgSortA = sh.Range("D1") '第一个排序字段,车牌号码
- Set rgSortB = sh.Range("I1") '第二个排序字段,交易时间
- rgData.Sort key1:=rgSortA, order1:=xlAscending, key2:=rgSortB, order2:=xlAscending, Header:=xlYes
- lngAllowableDiff = 10 '容许的时间差 为 10分钟
- arrData = rgData '区域转数组
- ReDim arrResult(LBound(arrData) To UBound(arrData), 1 To 1) As String '定义结果数组
- Set rgData = Nothing: Set rgSortA = Nothing: Set rgSortB = Nothing
-
- Set rgData = sh.Range("P1") '结果返回列
-
- '从第2行开始判断
- For lngRow = LBound(arrData) + 1 To UBound(arrData)
- strGantryID = Trim(arrData(lngRow, 2)) '门架编号
-
- strCarID = arrData(lngRow, 4) '车牌号码
-
- dateTradeTime = CDate(arrData(lngRow, 9)) '交易时间
-
- dblMoney = Val(arrData(lngRow, 7)) '交易金额
- strState = arrData(lngRow, 10) '交易状态
-
- If lngRow > LBound(arrData) + 1 Then
- strPreGantryID = Trim(arrData(lngRow - 1, 2)) '前一个门架编号
- strPreCarID = arrData(lngRow - 1, 4) '上一行车牌号码
- datePreTime = CDate(arrData(lngRow - 1, 9)) '前一个交易时间
- End If
-
- lngDiff = Abs(DateDiff("n", dateTradeTime, datePreTime)) '时间差
-
- '如果交易状态 为 失败,则处理
- If strState = "交易失败" Then
- arrResult(lngRow, 1) = CheckState(strCarID, strPreCarID, strGantryID, strPreGantryID, dblMoney, lngAllowableDiff, lngDiff)
- End If
- Next
-
-
- rgData.Resize(UBound(arrResult), 1) = arrResult
- End Sub
- Function CheckState(strCarID As String, strPreCarID As String, strGantryID As String, strPreGantryID As String, dblMoney As Double, lngAllowableDiff As Long, lngDiff As Long) As String
- Dim strGantryID_15 As String, strGantryID_14 As String '门架编号前15位 为 通道,前14位为 出入口
- Dim strPreGantryID_15 As String, strPreGantryID_14 As String
-
- '所有车牌号码为“默A00000”的,返回:“无车牌”
- If strCarID = "默A00000" Then
- CheckState = "无车牌"
- Exit Function
- End If
-
- strGantryID_15 = Mid(strGantryID, 1, 15) '门架编号前15位 为 通道
- strGantryID_14 = Mid(strGantryID, 1, 14) '门架编号前14位为 出入口
- strPreGantryID_15 = Mid(strPreGantryID, 1, 15) '门架编号前15位 为 通道
- strPreGantryID_14 = Mid(strPreGantryID, 1, 14) '门架编号前14位为 出入口
- '车牌号码相同的连续两条数据,一条交易成功,一条交易失败,且门架名称是相反的,时间在
- '返回“反向感应”
- If strCarID = strPreCarID Then '上下两条记录,车号相同
- If strGantryID_15 = strPreGantryID_15 Then '通道相同
- If lngDiff <= lngAllowableDiff Then '在容许的时间差内
- CheckState = "重复读取"
- Exit Function
- End If
- Else
- '出入口相同,并在容许的时间差内
- If strGantryID_14 = strPreGantryID_14 And lngDiff <= lngAllowableDiff Then
- CheckState = "反向感应"
- Exit Function
- End If
- End If
- End If
-
- '有交易金额,但显示交易失败,返回:“扣款失败”
- If dblMoney > 0 Then
- CheckState = "扣款失败"
- Exit Function
- End If
-
- CheckState = "****原因未知***"
-
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|