- 帖子
- 1447
- 主題
- 40
- 精華
- 0
- 積分
- 1471
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2025-5-5
|
10#
發表於 2023-3-14 11:59
| 只看該作者
回復 9# shan0948
謝謝前輩
後學練習VBA方式的解決方案如下,請前輩參考
執行結果:
Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$, T8$
Brr = Range([H4], Cells(Rows.Count, 1).End(3))
For i = 1 To UBound(Brr)
T1 = Val(Brr(i, 1)): T6 = Trim(Brr(i, 6)): T7 = Trim(Brr(i, 7))
T8 = Trim(Brr(i, 8))
Brr(i, 2) = "": Brr(i, 3) = "": Brr(i, 4) = "": Brr(i, 5) = ""
If T8 = "" Then GoTo 111
If T1 > 0 Then
N = i
If T8 = "起" Then Brr(N, 2) = T6
If T8 = "止" Then Brr(N, 3) = T6
ElseIf T8 = "起" Then
Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "、" & T6)
ElseIf T8 = "止" Then
Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "、" & T6)
If T7 = "離職" Then
Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "、" & T6)
End If
End If
If T8 = "起" And (T7 = "新進" Or T7 = "他訓轉入") Then
Brr(N, 4) = IIf(Brr(N, 4) = "", T6, Brr(N, 4) & "、" & T6)
End If
If T8 = "止" And T7 = "離職" Then
Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "、" & T6)
End If
111
Next
[K4].Resize(N, 5) = Brr
End Sub |
|