Find first empty cell in a column

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
148
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,219,006
Messages
6,145,714
Members
450,635
Latest member
Rookie3510

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
Back
Top