Generalize range with a changing variable

December 26, 2021

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

>Solution :

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