Macro to populate dates

lou78

New Member
Joined
Mar 13, 2018
Messages
10
Hi all - hoping someone can help please.
I need a macro that will auto populate date when adjacent cell complete as follows:-

AMOUNT column
DATE column
E
D
H
G
J
I
M
L

<tbody>
</tbody>
Thanks in advance for any for any help
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi,

What is the criteria that will determine what date to write in the cell ?
What is the logic of the macro ?
 
Last edited:
Upvote 0
hi - I need a date to enter whenever any value is entered - the spead sheets are large and this ensures the dates of monies paid are inserted correctly and also allows me to identify mistakes.
the value of the cell can be anything greater than 0
 
Upvote 0
So your wanting today's date entered into column B any time any value is entered into column A. Is that what you want.
 
Upvote 0
I need todays date entered but to stay that date for the following columns
AMOUNT
DATE
E
D
H
G
J
I
M
L

<tbody>
</tbody>
 
Upvote 0
Hi,

From what I understand, when someone writes Something in column A "Amount", it populates column B "Date". This is easy but the question is :
What date ? Today ? Tomorrow ?

Here's the code to populate col B with today's date when A is changed :
(Code to add in the Worksheet module).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Columns.Count > 1 Or Target.Column <> 1 Then Exit Sub
    
    Dim cel As Range
    
    For Each cel In Target
        
        If cel.Value > 0 and WorksheetFunction.IsNumber(cel.Value) Then
            cel.Offset(0, 1).Value = Date
        End If
        
    Next cel

End Sub
 
Last edited:
Upvote 0
So if the answer to my question is yes then try this.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-19-18 5:30 AM EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(, 1).Value = Date
End If
End Sub
 
Upvote 0
Try this:
Install as mentioned in previous post. This will work for columns E,H,J,M
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-19-18 6:00 AM EDT
If Target.Column = 5 Or Target.Column = 8 Or Target.Column = 10 Or Target.Column = 13 Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(, -1).Value = Date
End If
End Sub
 
Upvote 0
@ lou78
Welcome to the MrExcel board!

If your question has not been fully resolved by any of the suggestions so far, could you clarify the following questions?

1. If an amount is entered in an Amount column (& a date is entered in the relevant Date column) & subsequently the amount is changed, can you confirm that the Date column should be updated to the date the amount was changed?

2. If an amount is entered in an Amount column (& a date is entered in the relevant Date column) & subsequently the amount is deleted, should the Date be
a) left as it was?
b) updated to the date the deletion was made?
c) removed from the Date column?

3. Is it ever possible that multiple amounts could be entered at once (eg copy/paste), altered at once (eg entered with Ctrl+Enter) or deleted at once?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

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