| |
▼おにこ さん：
語と語の区切りは「、」ですか？
だとして、
（元） 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
|
|
kanabun
- 12/8/31(金) 19:21 -
