Sub test11()
Dim i As Integer
i = 15
ActiveWorkbook.ActiveSheet.Range("b2:b11") = "love"
ActiveWorkbook.ActiveSheet.Range("c2:c&i") = "Hate"
End Sub
This code line gives error 1004. How to change this line so that I can set i to different numbers every time. The above code is just a part of long code where range is used number of times.
ActiveWorkbook.ActiveSheet.Range("c2:c&i") = "Hate"
Thanks
CodePudding user response:
You must refer i
outside the quotes. This is because, VBA
assumes i
as a part of range
which is not the case (because i
is not the range itself, instead value
of i refers to an integer which is part of range). Hence, these kind of variable substitution
must be done outside quotes (""
)
Try this:
Sub test11()
Dim i As Integer
i = 15
ActiveWorkbook.ActiveSheet.Range("b2:b11") = "love"
ActiveWorkbook.ActiveSheet.Range("c2:c" & i) = "Hate"
End Sub