Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting values as they turn up in a column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting values as they turn up in a column
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 -- Regards, OssieMac "NDBC" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting values as they turn up in a column
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 110 then Target.Offset(0, 1) = WorksheetFunction.Max(Range("L5:L9")) + 1 or am I asking too much. Thanks again for responding. "OssieMac" wrote: 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 -- Regards, OssieMac "NDBC" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting values as they turn up in a column
another option is to have the target cell in another worksheet. The target is
in the same row in both sheets if this helps. "NDBC" wrote: 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 110 then Target.Offset(0, 1) = WorksheetFunction.Max(Range("L5:L9")) + 1 or am I asking too much. Thanks again for responding. "OssieMac" wrote: 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 -- Regards, OssieMac "NDBC" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting values as they turn up in a column
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 -- Regards, OssieMac "NDBC" wrote: another option is to have the target cell in another worksheet. The target is in the same row in both sheets if this helps. "NDBC" wrote: 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 110 then Target.Offset(0, 1) = WorksheetFunction.Max(Range("L5:L9")) + 1 or am I asking too much. Thanks again for responding. "OssieMac" wrote: 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 -- Regards, OssieMac "NDBC" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting values in a column | Excel Worksheet Functions | |||
Counting different values from same column | Excel Discussion (Misc queries) | |||
Counting two or more values from same column | Excel Discussion (Misc queries) | |||
Counting values in every other column | Excel Worksheet Functions | |||
counting everytime red cells turn white per column | Excel Programming |