Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dennis
 
Posts: n/a
Default Understanding .End(xlUp) (1,1)

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Understanding .End(xlUp) (1,1)

Hi Dennis,

I understand your confusion with the
Formula:
.End(xlUp
function. Let me explain it to you in detail.

The
Formula:
.End(xlUp
function is used to find the last used cell in a column. In your code, you are using it to find the last used cell in column A. The
Formula:
(1,1
after the
Formula:
.End(xlUp
function is used to move one cell down and one cell to the right of the last used cell in column A.

So, in your code, the
Formula:
.End(xlUp)(1,1
refers to the first empty cell in column A. This is where you are trying to enter the value "Test".

However, there is a mistake in your code. You are trying to assign the value "Test" to the
Formula:
.Value 
property of the
Formula:
.End(xlUp)(1,1
cell inside the With statement. This is not the correct syntax. You should first select the cell and then assign the value to it.

Here's the corrected code:
  1. Sub Test()
  2. With Sheets("Sheet2")
  3. .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "Test"
  4. End With
  5. End Sub

In this code, we first select the worksheet "Sheet2" using the With statement. Then, we use the
Formula:
.Cells 
property to refer to the last used cell in column A using the
Formula:
.Rows.Count 
and
Formula:
.End(xlUp
functions. We then use the
Formula:
.Offset 
function to move one cell down from the last used cell and assign the value "Test" to it.

I hope this helps you understand the
Formula:
.End(xlUp
function better.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

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


On Thu, 25 Nov 2004 16:05:01 -0800, Dennis
wrote:

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


  #4   Report Post  
Dennis
 
Posts: n/a
Default

Thanks for your time and knowledge!

Dennis

"Gord Dibben" wrote:

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


On Thu, 25 Nov 2004 16:05:01 -0800, Dennis
wrote:

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



  #5   Report Post  
Alan Beban
 
Posts: n/a
Default

Dennis wrote:
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"