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

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?

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.

In article <[email protected]>,
"Dan Morton" <[email protected]> wrote:

> 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?

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?


"JE McGimpsey" wrote:

> 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.
>
> In article <[email protected]>,
> "Dan Morton" <[email protected]> wrote:
>
> > 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?
>

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.





In article <[email protected]>,
"Dan Morton" <[email protected]> wrote:

> 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?
>
>
> "JE McGimpsey" wrote:
>
> > 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.
> >
> > In article <[email protected]>,
> > "Dan Morton" <[email protected]> wrote:
> >
> > > 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?
> >

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


"JE McGimpsey" wrote:

> 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.
>
>
>
>
>
> In article <[email protected]>,
> "Dan Morton" <[email protected]> wrote:
>
> > 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?
> >
> >
> > "JE McGimpsey" wrote:
> >
> > > 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.
> > >
> > > In article <[email protected]>,
> > > "Dan Morton" <[email protected]> wrote:
> > >
> > > > 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?
> > >
>

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?

"JE McGimpsey" wrote:

> 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.
>
>
>
>
>
> In article <[email protected]>,
> "Dan Morton" <[email protected]> wrote:
>
> > 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?
> >
> >
> > "JE McGimpsey" wrote:
> >
> > > 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.
> > >
> > > In article <[email protected]>,
> > > "Dan Morton" <[email protected]> wrote:
> > >
> > > > 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?
> > >
>