- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2023-9-21
|
29#
發表於 2021-9-10 11:45
| 只看該作者
回復 28# wayne0303
請測試看看，謝謝
Sub test()
Dim Arr, xD, T1$, T2$, T$, w1$, w2$, i&, j&, k%, R%, C%
Set xD = CreateObject("Scripting.Dictionary")
Set fs = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False: Application.DisplayAlerts = False
FPath = ThisWorkbook.Path
Set WB = Workbooks.Open(FPath & "\統計表.xlsx")
With Sheets(1)
If .FilterMode Then .ShowAllData
R = Range("f65536").End(3).Row
C = Rows("3:" & R).Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column
Arr = Range([f1], Cells(R, C))
For i = 1 To UBound(Arr)
If InStr(Arr(i, 1), "右螺") Then
For j = 1 To UBound(Arr, 2)
T = Arr(i + 2, j): If T = "" Then GoTo 95
xD(T & "_1") = Arr(i + 1, j)
95: Next
ElseIf InStr(Arr(i, 1), "左螺") Then
For j = 1 To UBound(Arr, 2)
T = Arr(i + 2, j): If T = "" Then GoTo 96
xD(T & "_2") = Arr(i + 1, j)
96: Next
ElseIf InStr(Arr(i, 1), "平") Then
For j = 1 To UBound(Arr, 2)
T = Arr(i + 2, j): If T = "" Then GoTo 97
xD(T & "_3") = Arr(i + 1, j)
97: Next
End If
Next
End With
WB.Close
Arr = Range("a1:a" & [a65536].End(3).Row)
For i = 1 To UBound(Arr)
If Arr(i, 1) = "" Then GoTo 98
If Left(Arr(i, 1), 1) = "L" Or Left(Arr(i, 1), 1) = "R" Then
w1 = Left(Arr(i, 1), 1): w2 = Mid(Arr(i, 1), 2, 1)
If Asc(w1) > 64 And Asc(w1) < 123 And Asc(w2) > 64 And Asc(w2) < 123 Then
If UCase(w1) = "L" Then
T1 = Mid(Split(Arr(i, 1), "仰")(0), 2)
T2 = Split(Split(Arr(i, 1), "角")(1), "°")(0)
T = T1 & "/" & T2 & "_" & 2: Arr(i, 1) = xD(T)
End If
If UCase(w1) = "R" Then
T1 = Mid(Split(Arr(i, 1), "仰")(0), 2)
T2 = Split(Split(Arr(i, 1), "角")(1), "°")(0)
T = T1 & "/" & T2 & "_" & 1: Arr(i, 1) = xD(T)
End If
Else
T = Split(Arr(i, 1), "轉")(0) & "_" & 3: Arr(i, 1) = xD(T)
End If
End If
98: Next
Range("b1").Resize(UBound(Arr)) = Arr
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub |
|
