|
A列数据源%上有带T数据全部删除,用C2&D2合并数据代替。
在%下,保留T1,找出坐标X最大,最小,Y最大,最小值,和M30,其他删除。有含有G85双坐标不计算,如A23,A24,不是坐标不计算如A26''M97,*", 计算坐标大小时,一定按6位数计算,要不结果不对,如A11,后面不加0,就不是Y最大,最后结果如B列,谢谢!!!
- Sub arr2()
- Dim arr(), arrResult(1 To 9, 1 To 1)
- Dim lLastRow&
- Dim i As Long
- Dim strTemp As String
- Dim arrTemp, xTemp As String, yTemp As String
- Dim lMin As Long, lMax As Long
- Dim objDicX As Object, objDicY As Object
- Set objDicX = CreateObject("scripting.dictionary")
- Set objDicY = CreateObject("scripting.dictionary")
- lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
- arr = Range("a1:a" & lLastRow).Value
- arrResult(1, 1) = arr(1, 1)
- arrResult(9, 1) = "M30"
- arrResult(2, 1) = [c2].Value & [d2].Value
- arrResult(3, 1) = "%"
- arrResult(4, 1) = "T1"
- Do
- i = i + 1
- Loop Until arr(i, 1) = "%"
- Do While arr(i, 1) <> "M30" And i <= UBound(arr)
- If Not arr(i, 1) Like "*G85*" Then
- strTemp = arr(i, 1)
- If strTemp Like "X*Y*" Then
- xTemp = Mid(strTemp, 2, InStr(strTemp, "Y") - 1)
- If Left(xTemp, 1) = "-" Then
- xTemp = Left(xTemp & "000000", 7)
- Else
- xTemp = Left(xTemp & "000000", 6)
- End If
- yTemp = Mid(strTemp, InStr(strTemp, "Y") + 1)
- If Left(yTemp, 1) = "-" Then
- yTemp = Left(yTemp & "000000", 7)
- Else
- yTemp = Left(yTemp & "000000", 6)
- End If
- objDicX(xTemp) = strTemp
- objDicY(yTemp) = strTemp
- End If
- End If
- i = i + 1
- Loop
-
- 'X值最大小值
- If objDicX.Count Then
- arrTemp = objDicX.keys
- For i = LBound(arrTemp) To UBound(arrTemp)
- arrTemp(i) = Val(arrTemp(i))
- Next
- lMin = WorksheetFunction.Match(WorksheetFunction.Min(arrTemp), arrTemp, False)
- lMax = WorksheetFunction.Match(WorksheetFunction.Max(arrTemp), arrTemp, False)
- arrTemp = objDicX.keys
- arrResult(5, 1) = objDicX(arrTemp(lMin - 1))
- arrResult(6, 1) = objDicX(arrTemp(lMax - 1))
- End If
-
- 'Y值最大小值
- If objDicY.Count Then
- arrTemp = objDicY.keys
- For i = LBound(arrTemp) To UBound(arrTemp)
- arrTemp(i) = Val(arrTemp(i))
- Next
-
- lMin = WorksheetFunction.Match(WorksheetFunction.Min(arrTemp), arrTemp, False)
- lMax = WorksheetFunction.Match(WorksheetFunction.Max(arrTemp), arrTemp, False)
- arrTemp = objDicY.keys
- arrResult(7, 1) = objDicY(arrTemp(lMin - 1))
- arrResult(8, 1) = objDicY(arrTemp(lMax - 1))
- End If
- Range("e1").Resize(UBound(arrResult), 1).Value = arrResult
- End Sub
复制代码
|
|