The following is valid in VBA
Range("K1:K5")
Why is the following not valid?
Dim nb As Long
Range("K1:K" + nb)
I also tried Range("K1:K" & nb) and even tried making nb a String instead.
Add a comment
|
2 Answers
Because the value of nb = 0 and the rows in excel start with 1
Try this
Dim nb As Long
Dim Rng As Range
nb = 2
Set Rng = Range("K1:K" & nb)
4 Comments
David Brossard
The issue wasn't the zero value. Your code worked but the following doesn't: Set Rng = Range("K" & nb1 & ":K" & nb2) assuming nb1=10 and nb2=15
Siddharth Rout
Nothing wrong with
Set Rng = Range("K" & nb1 & ":K" & nb2) What error are you getting?David Brossard
Oddly enough that only happens if nb1=nb2. That would make sense since I suppose a single cell is no longer a range...
Siddharth Rout
Umm even a single cell is a range. And it shouldn't give you an error even if nb1=bn2 :) Have you defined
Dim Rng As Range at the top?I'm not sure what the value is for nb.
Try using the Cells(RowIndex, ColumnIndex) notation where RowIndexand ColumnIndex are integers.
Sub RangeExemple()
Dim colIndex, colIndex2, rowIndex, rowIndex2 As Integer
colIndex = 7
colIndex2 = colIndex
rowIndex = 1
rowIndex2 = 5
Range(Cells(rowIndex, colIndex), Cells(rowIndex2, colIndex2)).Interior.ColorIndex = 37
End Sub
What do you want to do actually?
3 Comments
CustomX
that's the point ;) he probably didn't know you needed to define a value for nb
David Brossard
No :-) I wish... I had a value defined, I just didn't show that bit of code. Not defining a value would be a bit naïve...
sabotero
Well, the cells approach is very useful when looping, think about it!