Find first empty cell in a column

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
Is there quick way for Excel to find the first empty cell in a range, then enter a formula in it automatically when the user begins entering data?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210
Could you clarify a bit. Do you mean enter the formula in the same cell that the user is entering data into.

What would the formula be?
This message was edited by hedrijw on 2002-08-30 10:05
 

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
No, not the same cell. I have a spreadsheet that a user must update daily. I expect it to get lengthy, and I just want to automatically fill in the day of the week (required) when they open the workbook and enter the date. I don't want to extend the formula all through the spreadsheet, because it already takes forever to open a simple file on our network here. So, when a user opens the workbook and enters his date in column A, I want a macro (or function???) to find the row he's entering data in, and update the cell in columnB with
=text(A250,"mmmm")
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
The keystroke combination for 'tabbing' to an end of a region is End-Arrow.

Record a macro, use those key, in the direction you desire.

Sometimes it's best to start at the extreme end of the sheet and End-UpArrow; as the technique to determine the last row used.
 
L

Legacy 7259

Guest
On 2002-08-30 11:03, LoriD wrote:
No, not the same cell. I have a spreadsheet that a user must update daily. I expect it to get lengthy, and I just want to automatically fill in the day of the week (required) when they open the workbook and enter the date. I don't want to extend the formula all through the spreadsheet, because it already takes forever to open a simple file on our network here. So, when a user opens the workbook and enters his date in column A, I want a macro (or function???) to find the row he's entering data in, and update the cell in columnB with
=text(A250,"mmmm")

Where do you want the day of the week automatically entered?

I presume you want column B to display the month from the date input in the same row of column A :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
Target(1, 2).FormulaR1C1 = "=TEXT(RC[-1],""mmmm"")"
End If
End Sub
 

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
For N. Parker, jnr,: I have a two-fold question. Your solution worked so good, I'd like to customize it and use it for more things, but I don't understamd what it's doing. Can you take a minute to explain it to me?
Second, How can I customize it to enter the same formula in columns 2,3,4,etc?
 

Forum statistics

Threads
1,144,293
Messages
5,723,555
Members
422,503
Latest member
aarifmahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top