| |
こんにちは
「Test」は2個以上あった場合はV列W列と表示するようにしました
「Test1」は1個のみ表示です。
Sub Test()
Dim i As Long, ii As Long, Coun As Long, Co As Long
Dim Ch As Boolean, Ma As Variant, Da As Variant
With Worksheets("Sheet1")
Da = Range("A1", Range("A65536").End(xlUp)).Resize(, 20).Value
For i = 1 To UBound(Da)
Ch = True: Co = 22
For ii = 1 To 20
If Not IsEmpty(Da(i, ii)) Then
Coun = Application.CountIf(.Cells(i, 1).Resize(, 20), Da(i, ii))
If Coun > 1 Then
Ch = False
Ma = Application.Match(Da(i, ii), .Cells(i, 22).Resize(, Co - 21), 0)
If IsError(Ma) Then
.Cells(i, Co).Value = Da(i, ii)
Co = Co + 1
End If
End If
End If
Next ii
If Ch Then
.Cells(i, Co).Value = "なし"
End If
Next i
End With
End Sub
Sub Test_1()
Dim i As Long, ii As Long, Coun As Long
Dim Ch As Boolean, Da As Variant
With Worksheets("Sheet1")
Da = Range("A1", Range("A65536").End(xlUp)).Resize(, 20).Value
For i = 1 To UBound(Da)
Ch = True
For ii = 1 To 20
If Not IsEmpty(Da(i, ii)) Then
Coun = Application.CountIf(.Cells(i, 1).Resize(, 20), Da(i, ii))
If Coun > 1 Then
Ch = False
.Cells(i, 22).Value = Da(i, ii)
Exit For
End If
End If
Next ii
If Ch Then
.Cells(i, 22).Value = "なし"
End If
Next i
End With
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
- 06/5/24(水) 12:28 -
