CODEWITHSUNDEEP
excelvbarange
GI Joe
GI Joe

Reputation: 13

Range displays 2 different ways, but not sure why

I was wondering why the following displays different ways when I seem to be selecting the first row as the range.

'writes to first column(expected to write to first row)
Set Rng = Range("A1, B1, C1")

Rng(1) = "blah"
Rng(2) = "blah2"
Rng(3) = "blah3"

'writes to first row
Set Rng = Range("A1:C1")

Rng(1) = "blah"
Rng(2) = "blah2"
Rng(3) = "blah3"

Upvotes: 0

Views: 59

Answers (2)

Moist Carrot
Moist Carrot

Reputation: 43

There's a very simple explanation for this. First lets take a look at the parameters that Range Object accepts...

Range([Cell1], [Cell2])as Range, OR, Range(RowIndex, ColumnIndex)as Range, OR, Range(arg)

As you can see, Range can only accept 2 parameters; Cell 1, and Cell2; RowIndex, ColumnIndex, or an argument (which you specify in quotations).

So if we were to do this...

Set Rng = Range([A1], [B1], [C1]): Rng(1) = "blah": Rng(2) = "blah2": Rng(3) = "blah3"

We would get an error: "Compile error: Wrong number of arguments or invalid property assignment)

This is because we've just attempted to pass 3 parameters into an object that only accepts 2.

So with that being said, the reason your code runs without throwing errors, is you've defined your range within quotations (as an argument) and used commas in between. So you may be inputting ("A1, B1, C1") but excel sees it as ("A1").

So when you specify Rng(1) you're implementing a varient of the Range object, which means you're only specifying your RowIndex so its filling your values in rows 1, 2, 3.

However if we were to do this...

Set Rng = Range("A1")
Rng(1, 1) = "blah"
Rng(1, 2) = "blah2"
Rng(1, 3) = "blah3"

You would get the results you want because now you've specified your ColumnIndex along with the RowIndex.

Moving onto your second range ("A1:C1")...

When you use quotations in the Range Object, you're specifying it as an argument. So if you were to do this ("A1", "B1", "C1") you would get an error because you're trying to pass 3 arguments when it only accepts 2. So when you specify ("A1:C1") you're using a list separator : which tells excel that you're specifying a range of cells as compared to , which tells excel you're specifying 2 different cells as Range([Cell1], [Cell2]).

Hopefully this answered your question in detail.

Upvotes: 1

Phil
Phil

Reputation: 1464

First of all, you way of define cell values in Range object is not appropriate. Please try the following code, it will yield same output with two different Range definitions:

i = 1
For Each Cell In Rng
    Cell.Value = "blah" & i
    i = i + 1
Next

Regarding why your way of defining Range values works in that way, I tried two macros as follows. From the output of the macro, suppose the column number of the first range element (e.g. for A1 the number is 1, and for A1:D1 the number is 4) is n, we can see output of Rng(1) to Rng(n) will fill n columns first, then goes to the next row.

Attention should be paid that Rng(n) always exists whether you define a range or not. You can define or access Rng(100) at any time.

Sub Test1()
Set my_sheet1 = ThisWorkbook.Worksheets("Sheet1")
'writes to first column(expected to write to first row)
Set Rng = my_sheet1.Range("A1:B2, C1, D1")

i = 1
For Each Cell In Rng
    Cell.Value = "blah" & i
    i = i + 1

Next
Rng(3) = "3rd Rng"
For i = 1 To 20
    Rng(i) = "No." & i & " Rng"
Next

End Sub

Sub Test2()
Set my_sheet2 = ThisWorkbook.Worksheets("Sheet2")
Set Rng = my_sheet2.Range("A1:D1")  'Range with 4 columns

'loop with 4 columns in a row
i = 1
For Each Cell In Rng
    Cell.Value = "blah" & i
    i = i + 1
Next

For i = 7 To 20
    Rng(i) = "No." & i & " Rng"
Next

End Sub

Upvotes: 0

Related Questions