|
あと、Dictioanryオブジェクトを使った
こんな方法もありそうです。
これも参考まで。。。
Sub Try2()
Dim v1 As Variant 'Book1 A列
Dim v2 As Variant 'Book2 A列
Dim d2 As Variant 'Book2 G列 日付
Dim i As Long
Dim ss As String
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
'Book2のA列検索対象範囲
With Workbooks("Book2.xls").Worksheets(1)
With .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
v2 = .Value
d2 = .Offset(, 6).Value
End With
End With
For i = 1 To UBound(v2) 'Dictionaryの作成
ss = v2(i, 1)
If Not dic.Exists(ss) Then
dic(ss) = d2(i, 1) 'A列をKeyに、G列をItemにセット
End If
Next
'Book1のA列Loop範囲
With Workbooks("Book1.xls").Worksheets(1)
With .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
v1 = .Value
'500,000を引いてDictionaryにkeyがあるか調べる
For i = 1 To UBound(v1)
ss = v1(i, 1) - 500000
If dic.Exists(ss) Then
v1(i, 1) = dic(ss)
Else
v1(i, 1) = Empty
End If
Next
.Offset(, 8).Value = v1
End With
End With
Set dic = Nothing
End Sub
|
|