counting values as they turn up in a column

[NDBC]

H I J K L
Rider Laps Lap1 lap2 Place
No.

5 401 4 0:37:49 1:09:48 1
6 405 4 0:38:49
7 402 3 0:40:36 1:21:04 3
8 411 3 0:44:03 1:17:27 2
9 409 2 0:38:38

I am try ing to determine some code so that as the lap time turns up in
column k the riders place automatiucally turns up in column L. The place is
equal to the number of times in column k.

Thanks

 

[OssieMac]

Try the following.

Private Sub Worksheet_Change(ByVal Target As Range)

'Column K is column 11
If Target.Column = 11 Then
Target.Offset(0, 1) = _
WorksheetFunction.Max(Range("L5:L9")) + 1
End If
End Sub

Alternative code to include the entire column L when finding the existing max.
Target.Offset(0, 1) = _
WorksheetFunction.Max(Range("L:L")) + 1

 

[NDBC]

OssieMac Thanks. I just realised the values that are changing in column 11
are actually calculated by formulas from other sheets not inserted by code.
Is it possible to do something along the lines of if the value in column 11>0
then Target.Offset(0, 1) = WorksheetFunction.Max(Range("L5:L9")) + 1 or am I
asking too much.

Thanks again for responding.

 

[NDBC]

another option is to have the target cell in another worksheet. The target is
in the same row in both sheets if this helps.

 

[OssieMac]

Remove the previous code from the sheet module where the place numbers are to
get inserted.

Copy this code into the sheet where changes are being made. You said that
the row numbers are the same but if the column numbers are not the same then
you might have to adjust column 11.

Also have to edit "Sheet1" to match were the place numbers go.

No need to use Offset. I have simply address column 12 directly.

Private Sub Worksheet_Change(ByVal Target As Range)

'Column K is column 11
If Target.Column = 11 Then
With Sheets("Sheet1")
.Cells(Target.Row, 12) = _
WorksheetFunction.Max(.Range("L5:L9")) + 1
End With
End If
End Sub