|
▼ももかん さん:
すでにマナさんから模範解答がでていますので変化球です。
Sub Test()
Dim mSh As Worksheet
Dim pSh As Worksheet
Dim mList As Range
Dim pList As Range
For Each mSh In Worksheets
If Not mSh.Name Like "*単価" Then '月シート
Set pSh = Nothing
On Error Resume Next
Set pSh = Sheets(mSh.Name & "単価")
On Error GoTo 0
If Not pSh Is Nothing Then '単価シートあり
Set mList = mSh.Range("C4", mSh.Range("C" & Rows.Count).End(xlUp)).Offset(, 1).Resize(, 3)
With pSh.Range("A1").CurrentRegion
Set pList = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
End With
mList.Formula = "=IF($C4="""","""",VLOOKUP($C4,'" & pSh.Name & "'!" & pList.Address & ",COLUMN(B1),FALSE))"
mList.Value = mList.Value
End If
End If
Next
End Sub
|
|