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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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 ,
"Dan Morton" 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?