| |
▼迷える羊 さん：
>Set d(c.Value) = CreateObject("system.collections.arraylist")
>がオートメーションエラーとなってしまいます、、
arraylistが使えない環境ということですね。
Excelの標準機能だけを使うようにしました。
Sub test2()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim r As Range, c As Range
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1.Rows(1).Insert
ws1.Cells(1).Resize(, 2).Value = Array("t1", "t2")
ws2.Rows(1).Insert
ws2.Cells(1).Resize(, 2).Value = Array("t2", "t1")
Set ws3 = Worksheets.Add
Set r = ws3.Cells(1)
Set c = ws3.Cells(5).Resize(2)
c(2).Formula = "=countif(" & ws1.Name & "!A:A,B2)>0"
ws2.Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, c, r
Set r = r.CurrentRegion
With r.Worksheet.Sort
.SortFields.Clear
.SortFields.Add2 _
Key:=r.Columns(2), _
CustomOrder:=WorksheetFunction.TextJoin(",", True, ws1.Columns(1))
.SetRange r
.Header = xlYes
.Apply
End With
r.AdvancedFilter xlFilterCopy, , ws1.Cells(2)
Application.DisplayAlerts = False
ws3.Delete
Application.DisplayAlerts = True
ws1.Rows(1).Delete
ws2.Rows(1).Delete
ws1.Activate
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
