| |
こんにちは
Sub test()
Dim bk1 As Workbook
Dim bk2 As Workbook
Dim s As Worksheet
Dim p As String
Dim d As Range
p = ThisWorkbook.Path & "\"
Application.ScreenUpdating = False
Set bk1 = Workbooks.Open(p & "得意先p.xlsx")
Set d = bk1.Worksheets("Sheet1").Range("A:A")
Set bk2 = Workbooks.Open(p & "フォーマットi.xlsx")
For Each s In bk2.Worksheets
If s.Name = "惣菜" Then
Call test1(d, s, 4, 2, 34)
Else
Call test1(d, s, 5, 2, 20)
End If
Next
Application.ScreenUpdating = True
End Sub
Sub test1(tR As Range, sh2 As Worksheet, t As Long, i As Long, g As Long)
Dim v As Variant
Dim r As Long
Dim o As Long
o = i - t
With sh2
For r = t To .Range("T" & Rows.Count).End(xlUp).Row Step g
v = Application.Match(.Range("T" & r), tR, 0)
If IsError(v) = False Then
.Range("T" & r).Offset(o, -11).Value = _
sh2.Name & tR(v, 1).EntireRow.Range("BA1").Value
End If
Next
End With
End Sub
こんな感じでしょうか？
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
