Explain this bit of code

[YellowLabPro]

Hi All,
I am beginning to study VBA. This confuses me what is going on, could someone provide info on what the end results will yeild?

[VBA]With Sheets("Test").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -2)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell[/VBA]
Compared to

[VBA]
With Sheets("ParentData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -3)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
[/VBA]

Thanks,

YLP

[johnske]

Hi All,
I am beginning to study VBA. This confuses me what is going on, could someone provide info on what the end results will yeild?

[vba]With Sheets("Test").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -2)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell[/vba]
Compared to

[vba]
With Sheets("ParentData").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Cell.Offset(0, -3)
.Offset(1, 1) = Cell
.Offset(1, 2) = Cell
[/vba]

Thanks,

YLP

1) There is a "With" statement preceding both these bits of code, and this 'With' makes reference to another cell (maybe on another sheet).

2) With Sheets("Any sheets name").Range("A" & Rows.Count).End(xlUp) refers to the last cell in column A that contains data on that particular sheet (you could also use Range("A65536") instead of Range("A" & Rows.Count) but not all versions of Excel have the same number of rows. So Rows.Count ensures compatibility in all versions)

3) The 'dot' in front of .Offset(1, 0) means that .Offset(1, 0) is to be associated with the very last (i.e. previous) "With" statement.

4) Offset(1, 0) means, "the cell that is located one (1) row below this cell (i.e. the last cell in column A) and 0 columns to the left or right of this cell"

5) The 1, 0 and 1, 1 and 0, -3 refer to the offset distances with respect to the number of rows and columns from the current cell. If the first number is positive, the offset is the given number of rows below the current cell, and if the first number is negative, the offset is the given number of rows above the current cell. Similarly, the second number refers to the number of columns - and in this case positive is to the right, negative is to the left. When Offset is omitted on the RHS it simply means the Cell itself, and is exactly the same meaning as Cell.Offset(0, 0)

6) .Offset(1, 0) = Cell.Offset(0, -2) means that whatever is in .Offset(1, 0) is to be replaced with whatever's in Cell.Offset(0, -2)

HTH


Edit: .Offset(1, 0) = Cell.Offset(0, -2) could also be written as .Offset(1, 0).Value = Cell.Offset(0, -2).Value... But as 'Value' is simply the default property it can be omitted because Visual Basic always uses the default property unless specified otherwise by you. (Read your VBA Help files and bone up on all the default properties for everything - they can save you a lot of typing - and the less typing there is, the lesser the chance there is a typing error )