|
▼おにこ さん:
語と語の区切りは「、」ですか?
だとして、
(元) AA列
たぬき<みどり>、きつね
きつね、たぬき
たぬき<赤>、きつね<茶色>
たぬき、きつね<みどり>
(移動後)
AA列 AH列
きつね たぬき<みどり>
きつね、たぬき
たぬき<赤>、きつね<茶色>
たぬき きつね<みどり>
となればいいのですか?
Sub Try2()
Dim r As Range, c As Range
Dim ss As String, aa, ah
Dim i As Long
Dim List1 As Object
Dim List2 As Object
Set List1 = CreateObject("Forms.Combobox.1")
Set List2 = CreateObject("Forms.Combobox.1")
For Each c In Range("AA1", Cells(Rows.Count, "AA").End(xlUp))
ss = c.Value
If InStr(ss, "<") > 0 Then
aa = Split(ss, "、") 'セルの文字列を「、」で分割
List1.List = aa
List2.List = aa
With List1
For i = .ListCount - 1 To 0 Step -1
If .List(i) Like "*<*>*" Then
.RemoveItem i
Else
List2.RemoveItem i
End If
Next
End With
aa = ""
ah = ""
Select Case List1.ListCount
Case Is = 1: aa = List1.List(0)
Case Is > 1: aa = Join(Application.Transpose(List1.List), "、")
End Select
Select Case List2.ListCount
Case Is = 1: ah = List2.List(0)
Case Is > 1: ah = Join(Application.Transpose(List2.List), "、")
End Select
c.Value = aa
c.Offset(, 7).Value = ah
End If
Next
End Sub
|
|