0

## Excel VBA對照字串並自動按順序排列

1,2,3,B1,B2,B3,4,5,B4,A1,A2,A3,6,7,8,A4,A5,9。

no1kent iT邦新手 5 級 ‧ 2019-06-12 21:36:03 檢舉

1, 2, N1, N2, 3, N3, A1, A2, T, B

2, 3, N2, N3, N1 A1 T 1, B, A2

### 2 個回答

2
Neish
iT邦研究生 1 級 ‧ 2019-06-12 11:00:43

``````Sub test()

Dim str_col As Integer
Dim end_col As Integer

Dim i As Integer
Dim j As Integer
Dim k As Integer

'找最末欄
end_col = Rows(3).Find("").Column - 1

For str_col = 1 To end_col

If Cells(3, str_col) = "早" Then
i = i + 1
Cells(4, str_col) = i

ElseIf Cells(3, str_col) = "晚" Then
j = j + 1
Cells(4, str_col) = "A" & j

ElseIf Cells(3, str_col) = "夜" Then
k = k + 1
Cells(4, str_col) = "B" & k

End If

Next

End Sub
``````
0
paicheng0111
iT邦高手 1 級 ‧ 2019-06-12 13:59:28

``````Sub test()
Dim myRng As Range, rng As Range
Dim i As Long, j As Long, k As Long

Set myRng = Range("A3:R3")

For Each rng In myRng
Select Case rng
Case "早"
i = i + 1
rng.Offset(1).Value = i
Case "晚"
j = j + 1
rng.Offset(1).Value = "A" & j
Case "夜"
k = k + 1
rng.Offset(1).Value = "B" & k
Case Else
End Select
Next rng
End Sub
``````

no1kent iT邦新手 5 級 ‧ 2019-06-12 18:56:02 檢舉