VB Question on syntax

[arkusM]

I have found a bit of code to track changes in a spreadsheet (at Ozgrid)

But I am puzzled as to this bit here

.Cells(.Rows.Count, x).End(xlUp)[COLOR=blue][B](2, 1)[/B][/COLOR]

I don't understand what th blue blodness is doing. Is it shorthand for an Offset?

 

[RoryA]

Not quite - it is shorthand for Cells(2, 1)

 

[arkusM]

Not quite - it is shorthand for Cells(2, 1)

thanks Rory,

So the red bit provides a relative reverence to the blue bit?

.Cells(.Rows.Count, x).End(xlUp)(2, 1)

Acting like an "offset"?

 

[MickG]

Hi, It achieves the same result as setting a range as an Range Object as below.
Both bits of code produce the same Address.

Dim t, m, Rng As Range
Set Rng = Range("A4:C10")
m = Rng.Cells(2, 3).Address
'm = $C$5

 'or

t = Range("A4:C10")(2, 3).Address
't= $C$5

Regards Mick

 

[arkusM]

Hi, It achieves the same result as setting a range as an Range Object as below.
Both bits of code produce the same Address.

Dim t, m, Rng As Range
Set Rng = Range("A4:C10")
m = Rng.Cells(2, 3).Address
'm = $C$5
 
 'or
 
t = Range("A4:C10")(2, 3).Address
't= $C$5

Regards Mick

Oh Ok. Thanks. I think that make some sense. I will continute to experiment and wrap my mind around this. I think this would have saved my a bunch of typing in my coding attempts earlier!! HAHA

 

[arkusM]

thanks Rory,

So the red bit provides a relative reverence to the blue bit?


Acting like an "offset"?

Haha, that is a funny typo!! Not entirely inappropriate, but still funny!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[RoryA]

The slightly odd thing is that the Cells references are not constrained to the range to which you apply them, so you can use:

Range("A1").Cells(2,2)

to get to B2 for example. It's like an offset except 0 based rather than 1 based - i.e. Cells(1,1) is the equivalent of Offset(0,0)

 

[arkusM]

The slightly odd thing is that the Cells references are not constrained to the range to which you apply them, so you can use:

Range("A1").Cells(2,2)

to get to B2 for example. It's like an offset except 0 based rather than 1 based - i.e. Cells(1,1) is the equivalent of Offset(0,0)

Ok. That is cool. Thanks.
This is obviously not my code, but I am adapting it.
Is this syntax a good or bad practice? It seems to have some useful application, at least to my mind. But I don't want to use it if it is a "bad habit".

 

[RoryA]

I would personally specify the Cells property rather than just using the parentheses.

 

[arkusM]

I would personally specify the Cells property rather than just using the parentheses.

Cool. Thanks. I guess if the original code had done that I would not have asked my question in the first place. I asked a redendant question!!
Thank you for your patience and help!!

Cheer!

Mark