| |
おじゃまします
metabeaux さんの
>いくつかある方法
のうちの（たぶん）ひとつです。
Dictionaryを使う方法です。
Sub Try1() '両Book はともに開かれているものとします.
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim dic As Object
Dim r As Range
Dim v, u, w
Dim i As Long, j As Long, jj As Long
Set WS1 = Workbooks("対象一覧.xls").Worksheets(1)
Set WS2 = Workbooks("マスター.xls").Worksheets(1)
v = WS1.[A1].CurrentRegion.Resize(, 1).Value
With WS2.UsedRange
u = .Resize(, 1).Value
jj = .Columns.Count
Set r = .Offset(, 1).Resize(, jj)
w = r.Value
End With
With CreateObject("Scripting.Dictionary")
'----------- 対象一覧 A列(名前) を辞書に登録する
For i = 1 To UBound(v)
.Item(v(i, 1)) = Empty
Next
'-------- マスター照合
For i = 1 To UBound(u)
If Not IsEmpty(u(i, 1)) Then
If .Exists(u(i, 1)) Then
For j = 2 To jj
If IsEmpty(w(i, j)) Then
w(i, j) = 9
Exit For
End If
Next
End If
End If
Next
End With
r.Value = w
End Sub
|
|
kanabun
- 10/12/2(木) 17:19 -
