|
こんにちは
直すとしたら、
Sub test()
Dim V As Long
Dim i As Long
For V = 5 To 59
i = V - 3
Sheets("データシート1").Cells(1, V).Activate
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC1,構成!R1C1:R200C80," & i & ",0))" _
& "=TRUE,""0"",VLOOKUP(RC1,構成!R1C1:R200C80," & i & ",0))"
Next
End Sub
と、変数を文字列に「&」で連結します。
ただ、VBA的には複数範囲に数式をセットする場合は一気にするのがいいと思います。
Sub test1()
Dim V As Range
Dim s As String
Set V = Worksheets("データシート1").Range("E1").Resize(, 55)
s = Worksheets("構成").Range("A1:CE200").Address(1, 1, True, 1)
With V
.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC1," & s & ",COLUMN()-3,0))" _
& "=TRUE,""0"",VLOOKUP(RC1," & s & ",COLUMN()-3,0))"
' .Value = .Value
End With
End Sub
|
|