General VBA question - Range.Cells

[pella88]

Hello everyone,

I got into discussion with my friend a couple of days ago in regards to Range.Cells and its usage.

How I understood this method so far is that, if for example you have Range("A1:C3") and you say Range.Cells(2,2) it would refer to second row second column in my range, which would be B2.

However, my friend has been using this method as an offset, by referring to cell outside this range.. For example, he has Range("A1") and if he does Range.Cells(2,2) it would still select B2, even though it is not part of the range.

My assumption was that Excel is assuming something and this is why it behaves as my friend expects it, however, I just tried having Range("A1:C3") and referring to Cells(10,10) and it still worked.

Anyone can give some explanation about the situation... Ty in advance

 

[Joe4]

Range.Cells(2,2)

is not a valid statement and will return errors.

If you have a range variable, you could apply that logic to it, like this:

    Dim rng As Range
    Set rng = Range("E10:M100")
    MsgBox rng.Cells(2, 2).Address

That would return "$F$11".

That is because we created a range variable, assigned a value to it, and then used Cells on that.
But "Range" by itself is nothing. In using the reserved word "Range", it is expecting the row/column reference after it, i.e. Range("A1")
(by the way, you should NEVER used reserved words like "Range" as the name of variables, procedures, or functions).

Note that you can use Cells(row,column) by itself, i.e.
Range("B4") is the same as Cells(4,2) or Cells(4,"B")

 

[pella88]

Regarding your first comment: I know, i just shortened. This is why I emphasized the range I have... In any case... I still don't understand why his way returns the same value as mine?

 

[Joe4]

Regarding your first comment: I know, i just shortened.

Please don't do that. It is very confusing, and we do not know why you did that (if it was on purpose, or if you are using it wrong).

Can you work out a detailed example with all the correct syntax for us? And then maybe we will see more clearly what you are talking about?

 

[Norie]

If your friend is using code like this I think I'd recommend they don't.

Set rng = Range("A1")

MsgBox rng.Cells(2,2).Address

Even though that's a simple example it's a little confusing, what if the range/offset involved weren't so simple?

 

[pella88]

Sorry for that, I hopped that my question was clear enough, as I am not asking if my code is correct or not, but why does both examples give same result

My friends example = returns $B$2:

Sub Example1()

Dim wb as Workbook
Dim ws as Excel.Worksheet
Dim rng as Range

set wb = ThisWorkbook
set ws = wb.Sheets("Sheet1")
set rng = ws.Range("A1")

Msgbox rng.Cells(2,2).Address

End sub

My example = returns $B$2:

Sub Example2()

Dim wb as Workbook
Dim ws as Excel.Worksheet
Dim rng as Range

set wb = ThisWorkbook
set ws = wb.Sheets("Sheet1")
set rng = ws.Range("A1:C3")

Msgbox rng.Cells(2,2).Address

End sub

My understanding of rng.Cells is that it should work as my example, and not as his.

 

[pella88]

If your friend is using code like this I think I'd recommend they don't.

Set rng = Range("A1")

MsgBox rng.Cells(2,2).Address

Even though that's a simple example it's a little confusing, what if the range/offset involved weren't so simple?

My point exactly... I would always use offset, and this is why I am worried that his approach doesn't make sense, or that Excel assumes what my friend is trying to do (same as you say: Dim rng, but do not specify type, Excel will assume its type the first time you assign him value)...

 

[Norie]

I actually declared rng, forgot to include the declaration when posting.

I was more worried about something like this,

Dim rng As Range

    Set rng = Range("F3:H43")

    MsgBox Rng.Cells(57, 102).Address

or even something like this,

Dim rng As Range

    Set rng = Range("F3:H43")

    Set rng = rng.Cells(10, 10)


    MsgBox rng.Cells(324, 4333).Address

or even this.

Dim rng As Range

    Set rng = Range("D1")

    Set rng = rng.Cells(10, 10).Cells(123, 32)

    MsgBox rng.Address

PS I know these examples are ridiculous.

 

[Rick Rothstein]

I got into discussion with my friend a couple of days ago in regards to Range.Cells and its usage.

How I understood this method so far is that, if for example you have Range("A1:C3") and you say Range.Cells(2,2) it would refer to second row second column in my range, which would be B2.

However, my friend has been using this method as an offset, by referring to cell outside this range.. For example, he has Range("A1") and if he does Range.Cells(2,2) it would still select B2, even though it is not part of the range.

My assumption was that Excel is assuming something and this is why it behaves as my friend expects it, however, I just tried having Range("A1:C3") and referring to Cells(10,10) and it still worked.

Anyone can give some explanation about the situation... Ty in advance

When you reference something like Range("F3:M9")(3,5), the only thing the expression uses from Range object is cell F3. In effect, that becomes an "anchor point" establishing what is essentially a new grid whose cells are numbered starting at 1; hence, Range("F3:M9")(1,1) is cell F3 and Range("F3:M9")(3,5) is cell J5. This is actually a shortcut notation for this...

Range("F3:M9").Cells(3,5)

or, more simply, this...

Range("F3").Cells(3,5)

This notation differs from using the Offset property in that Offset(1,1) is one row and one column away from the reference range and refers to the fully specified range. So this...

Range("F3:M9").Offset(3,5)

references the full range offsetted to K6:R12 and not a single cell offset as the beginning notation does. I am not sure how clear what I wrote above will be to you, but it should give you a good start in understanding what is going on.

 

[MARK858]

http://www.cpearson.com/excel/cells.htm