|
こんにちは
最初の表をSheet1とし在庫表をSheet2とします
Sheet2(在庫側)は事前に作成している事。
Sub Test()
Dim Da As Variant, Co As Long, i As Long, ii As Long
Dim Fi As Range, Ad As String, Con As Long
With Worksheets("Sheet2")
Co = .Range("IV1").End(xlToLeft).Column
Da = .Range("A2", .Range("A65536").End(xlUp)).Resize(, Co).Value
Con = 1
With Worksheets("Sheet1")
For i = 2 To Co
For ii = 1 To UBound(Da)
Set Fi = .Columns(Con).Find(Da(ii, 1), , xlValues, xlWhole)
If Not Fi Is Nothing Then
Ad = Fi.Address
Do
Set Fi = .Columns(Con).FindNext(Fi)
If IsEmpty(Fi.Offset(, 1).Value) Then
Da(ii, i) = Da(ii, i) + 1
End If
Loop Until Ad = Fi.Address
Set Fi = Nothing
End If
Next ii
Con = Con + 2
Next i
End With
.Range("A2", .Range("A65536").End(xlUp)).Resize(, Co).Value = Da
End With
End Sub
|
|