Understanding .End(xlUp) (1,1)

[Guest]

Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?


Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis

 

[Gord Dibben]

Dennis

End(xlUp) starts looking at row 65536 and moves up until it finds data.

The .Offset(1,1) goes back down one row and over one column.

..Offset(1,0) would select last empty cell in column A

Sub Test()
With Sheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = "Test"
End With
End Sub

Places the word test into column B in last empty row of Column A

Gord Dibben Excel MVP

 

[Guest]

Thanks for your time and knowledge!

Dennis

Dennis

End(xlUp) starts looking at row 65536 and moves up until it finds data.

The .Offset(1,1) goes back down one row and over one column.

..Offset(1,0) would select last empty cell in column A

Sub Test()
With Sheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Value = "Test"
End With
End Sub

Places the word test into column B in last empty row of Column A

Gord Dibben Excel MVP

 

[Alan Beban]

Using XL 2003 & 97

Apparently, I just do not get it.

I wish to enter "Test" into the first empty cell in Column A.

Tried .Value and .FormulaR1C1 to no avail.

The code does not bomb but "Test" does not show up anywhere on
Worksheet("Sheet2").

Is xlUp(1, 1) kind-of like Offset(1, 1)?


Sub Test()

With Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(1, 1).Value = "Test"
End With

End Sub

TIA Dennis

xlUp(1,1) is the same as xlUp.Item(1,1); omitting "Item" is an
equivalent syntax. xlUp(1,1) is also equivalent to xlUp.Offset(0,0).
Said differently, Offset is 0-based and the Item Properrty is 1-based.

Alan Beban