PDA

View Full Version : Solved: FUNCTION() to check two parameters and update cell with date



cchris_uk
02-20-2008, 10:09 AM
Hi,

I need a function that can be placed in cells in a column that:

Checks the cell in 'A' column on the current row for a valid date.
If no date exists, check a range of cells on the current row (eg B, C, D, E)
If the selected range contains data, then enter the current date in column A else do nothing.

At no time should a date be overwritten, it should only be inserted if it did not exist in the first place and the selected range contains data.

If this can be created as a function, then that would be great, as I cannot rely on the users of this sheet to remember to run a macro each time they enter data and I need the spreadsheet to keep a record of the date data is entered for each row.

Regards,
Chris

ProteanBeing
02-20-2008, 10:14 AM
I would use an event. Either Change or SelectionChange. They behave slightly different so try one and if it don't work quite right cut and paste to the other.

mdmackillop
02-20-2008, 05:12 PM
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column > 5 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Application.CountA(Cells(Target.Row, 2).Resize(, 4)) > 0 Then
If Cells(Target.Row, 1) = "" Then
With Cells(Target.Row, 1)
.Value = Date
.NumberFormat = "m/d/yyyy"
End With
End If
End If
End Sub

cchris_uk
02-21-2008, 12:54 AM
Sorry if I seem dense, but how do I implement this code?

Chris

mdmackillop
02-21-2008, 01:08 AM
You place it in the Worksheet module. This code will not update existing data, but will add dates for new entered data.

cchris_uk
02-21-2008, 01:24 AM
Ah wow, thanks, works like a charm.
Chris

ProteanBeing
02-21-2008, 12:37 PM
It's what microsoft calls an event. The code is automatically run when the event is triggered. The change event, for example, is triggered when the worksheet is updated. Target is the cells that is being updated.

cchris_uk
02-21-2008, 02:29 PM
Once I had the example, I was able to look up the references and figure out what was happening.

All the advice over the last few weeks has been immensely helpful, I have been reworking seriously convoluted recorded macros and lookups that are spread over several sheets.

Chris

mdmackillop
02-21-2008, 03:45 PM
When you have other code, you may want to add
Application.EnableEvents = False at the start of your code.
This prevents Event code running while your code makes changes to the sheet. You MUST reset to True at the end of your sub, as it will stop all Event macros from running. Unlike some other settings, this does not reset when the sub ends.