|
▼vba初心者 さん:
関数を使わない処理案です。
Sub test2()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim dic As Object
Dim v As Variant
Dim i As Long
Dim c As Range
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = Workbooks("Book2.xlsx").Sheets("Sheet1")
Set dic = CreateObject("Scripting.Dictionary")
'Book2のA列の値を格納
For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
dic(c.Value) = True
Next
With sh1.Range("v2", sh1.Range("v" & Rows.Count).End(xlUp))
'Book1 の U,V列を配列に格納
v = .Cells.Offset(, -1).Resize(, 2).Value
'配列内で重複チェック
For i = LBound(v, 1) To UBound(v, 1)
If dic.exists(v(i, 2)) Then
v(i, 1) = 1933
Else
v(i, 1) = Empty
End If
Next
'Book1に書き戻し
.Cells.Offset(, -1).Resize(, 2).Value = v
End With
End Sub
|
|