How to get date entered recording first time condition is true?

D

Dan Morton

Would like a date entered in a column the first time a condition is met
calculated on several other columns in the same row. How could this be
implemented?
 
J

JE McGimpsey

You could modify the circular reference formula at

http://www.mcgimpsey.com/excel/timestamp.html

to substitute your conditional statement for A1="".

Or you could use something like the event macro shown there, but using
the Calculation event instead, for instance:

Private Sub Worksheet_Calculate()
Dim rCheck As Range
Dim rCell As Range
On Error Resume Next
Set rCheck = Range("A:A").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rCheck Is Nothing Then
For Each rCell In rCheck
With rCell
If .Offset(0, 1) = True And .Offset(0, 2) > 10 Then
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End If
End With
Next rCell
End If
End Sub

Modify to suit your conditions.
 
D

Dan Morton

I'm not that familiar with macros or visual basic so excuse the questions
regarding the suggestion you provided.
in the line rcheck =Range("A:A") woulld i replace A:A with the range the
cells I want to check the condition of? Is .SpecialCell(xlcellTypeBlanks) a
comment?
 
J

JE McGimpsey

I assumed that the dates should go into column A.

Range("A:A").SpecialCells(xlCellTypeBlanks) returns the collection of
blank cells in column A (well, only the part of column A that's in the
used range of cells). There's no sense checking cells in column A that
are already filled, since you only want to put a date in the cell the
*first* time that the conditions in that row are true.

For each blank cell, then, the conditions are tested. Since you didn't
mention the conditions, I made two up:

If .Offset(0, 1).Value = True And .Offset(0, 2).Value > 10 Then

which if the blank cell was A32, would test B32 to see if it held the
value True, and would test C32 to see if it's value is > 10. You'd need
to substitute your conditions here .

If the conditions are both True, then that blank if filled in with the
date. If not, the For...Next loop goes on to the next blank cell.
 
D

Dan Morton

Thanks so much for your help. I used the code you provided first as a macro
and then put it in as worksheet calculate event code. It appears to do
exactly what I wanted.

Here's what I ended up using.

Private Sub Worksheet_Calculate()
Dim rCheck As Range
Dim rCell As Range
On Error Resume Next
'next line gets range containing just blank cells
Set rCheck = Range("P:p").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rCheck Is Nothing Then
For Each rCell In rCheck
With rCell
If .Offset(0, 1) = "QUALIFIED" Then
.NumberFormat = "dd-mmm-yy"
.Value = Date
End If
End With
Next rCell
End If
End Sub

I'm not sure what the lines
1. On Error Resume Next
and
2. On Error GoTo 0 (where's 0?)
and
I3. f Not rCheck Is Nothing (is nothing a keyword defined in VB?)

Again, many thanks,

Dan Morton
 
D

Dan Morton

I thought I was home free but when I turned protection on the sheet back on I
can't get it to work. I tried inserting qn activesheet.unprotect before the
code and an activesheet.protect after the code segment but it that didn't
seem to fix it. Any ideas?
 
Top