Getting .End(xlDown).Offset(1, 0).Select to work correctly

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
579
Row 100 contains the Text "Comments". With the cursor always on row 26 of the active column,
the following code is not working correctly
Code:
Cells(74, ActiveCell.Column).End(xlDown).Offset(1, 0).Select
 ActiveCell.Value = TextBox1.Value

It should put a new value from Textbox1 of Userform1 below any previously entered value
below row 100(Comments), and keep doing that. What it's doing
is erasing the value in row 101 each time and replacing it with the new value from Textbox1 instead
of putting it BELOW(i.e., the 1st blank cell) the previously entered value(s) below Comments(row 100)

I can't see anything wrong with this simple code operation. Csn anyone please show me what
is wrong wirh this code ?


Thanks for anyone's help

cr
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
If your intent is to find the first blank cell then maybe
Rich (BB code):
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1, 0).Select


Test and see if your experience is any different
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,788
Office Version
  1. 365
Platform
  1. Windows
Your code works for me , but try this instead

Do not need to select to put something in a cell
Start at 100 if it needs to be below 100

Code:
Cells(100, ActiveCell.Column).End(xlDown).Offset(1, 0).Value = TextBox1.Value
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
Your code works for me , but try this instead

Do not need to select to put something in a cell
Start at 100 if it needs to be below 100

Code:
Cells(100, ActiveCell.Column).End(xlDown).Offset(1, 0).Value = TextBox1.Value

Valid point surely. Selection actually slows down codes and it is redundant as you have clearly demonstrated
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
579
If your intent is to find the first blank cell then maybe
Rich (BB code):
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(1, 0).Select


Test and see if your experience is any different

Tested, tested and tested again. This works every time. Many thanks for all your help.
cr
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,468
Members
409,883
Latest member
asharris90
Top