I have the following code which is trying to determine the maximum value in
the whole column beside the target cell, then add 1 to it and enter it in the
column beside the target cell. It doesn't give an error but it is not
selecting the right values

Target.Offset(0, 1) = WorksheetFunction.Max(Range(Target.Column + 1 & ":" &
Target.Column + 1)) + 1

Thanks

not sure what you're trying to do, but give this a try

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngfound As Range
If Target.Count = 1 Then
With Columns(Target.Offset(, 1).Column)
Set rngfound =
..Find(WorksheetFunction.Max(Columns(.Column)), _
lookat:=xlWhole, LookIn:=xlValues)
If Not rngfound Is Nothing Then
Range(rngfound.Address).Value = _
Range(rngfound.Address).Value + 1
End If
End With
End If
End Sub


--

Gary Keramidas
Excel 2003


"NDBC" wrote in message
...
I have the following code which is trying to determine the maximum value in
the whole column beside the target cell, then add 1 to it and enter it in
the
column beside the target cell. It doesn't give an error but it is not
selecting the right values

Target.Offset(0, 1) = WorksheetFunction.Max(Range(Target.Column + 1 & ":"
&
Target.Column + 1)) + 1

Thanks

thanks gary. I don't pretend to understand that code and I haven't tried it.
What I am trying to do is calculate race competitors place every lap as the
times are entered. ie. first time entered for the lap is 1st, 2nd time is 2nd
etc. I only have 100 competitors per worksheet so the code below works for me
but i just wondering if there was an easy way to select the whole column.

If Target.Column = 11 And (Target.Column Mod 2) = 1 Then
Target.Offset(0, 1) = WorksheetFunction.Max(Range(Cells(5,
Target.Offset(0, 1).Column), Cells(104, Target.Offset(0, 1).Column))) + 1

One thing you might be able to explain for me is the If not ......is nothing
then statement. Sounds like a double negative to me and I don't understand
the logic.

I'm sure your code will do the job but in my instance I had less code and it
worked so I haven't used yours. Thanks


"Gary Keramidas" wrote:

not sure what you're trying to do, but give this a try

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngfound As Range
If Target.Count = 1 Then
With Columns(Target.Offset(, 1).Column)
Set rngfound =
..Find(WorksheetFunction.Max(Columns(.Column)), _
lookat:=xlWhole, LookIn:=xlValues)
If Not rngfound Is Nothing Then
Range(rngfound.Address).Value = _
Range(rngfound.Address).Value + 1
End If
End With
End If
End Sub


--

Gary Keramidas
Excel 2003


"NDBC" wrote in message
...
I have the following code which is trying to determine the maximum value in
the whole column beside the target cell, then add 1 to it and enter it in
the
column beside the target cell. It doesn't give an error but it is not
selecting the right values

Target.Offset(0, 1) = WorksheetFunction.Max(Range(Target.Column + 1 & ":"
&
Target.Column + 1)) + 1

Thanks

The following is a modification of the code you posted to include the entire
column instead of a specific range.

Target.Offset(0, 1) = WorksheetFunction.Max(Columns(Target.Column + 1)) + 1

Your other question "If Not rngfound Is Nothing"

You you use the code
If rngfound Is Nothing
This means that because rngfound was not found then the variable rngfound is
nothing or does not have a value.

Therefore if rngfound is Not nothing then it must be the opposite to nothing
because it is something (or does have a value). Therefore Not Nothing means
it was found.




--
Regards,

OssieMac


"NDBC" wrote:

thanks gary. I don't pretend to understand that code and I haven't tried it.
What I am trying to do is calculate race competitors place every lap as the
times are entered. ie. first time entered for the lap is 1st, 2nd time is 2nd
etc. I only have 100 competitors per worksheet so the code below works for me
but i just wondering if there was an easy way to select the whole column.

If Target.Column = 11 And (Target.Column Mod 2) = 1 Then
Target.Offset(0, 1) = WorksheetFunction.Max(Range(Cells(5,
Target.Offset(0, 1).Column), Cells(104, Target.Offset(0, 1).Column))) + 1

One thing you might be able to explain for me is the If not ......is nothing
then statement. Sounds like a double negative to me and I don't understand
the logic.

I'm sure your code will do the job but in my instance I had less code and it
worked so I haven't used yours. Thanks


"Gary Keramidas" wrote:

not sure what you're trying to do, but give this a try

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngfound As Range
If Target.Count = 1 Then
With Columns(Target.Offset(, 1).Column)
Set rngfound =
..Find(WorksheetFunction.Max(Columns(.Column)), _
lookat:=xlWhole, LookIn:=xlValues)
If Not rngfound Is Nothing Then
Range(rngfound.Address).Value = _
Range(rngfound.Address).Value + 1
End If
End With
End If
End Sub


--

Gary Keramidas
Excel 2003


"NDBC" wrote in message
...
I have the following code which is trying to determine the maximum value in
the whole column beside the target cell, then add 1 to it and enter it in
the
column beside the target cell. It doesn't give an error but it is not
selecting the right values

Target.Offset(0, 1) = WorksheetFunction.Max(Range(Target.Column + 1 & ":"
&
Target.Column + 1)) + 1

Thanks

mine wouldn't do what you wanted because i misunderstood the goal.

--

Gary Keramidas
Excel 2003


"NDBC" wrote in message
...
thanks gary. I don't pretend to understand that code and I haven't tried
it.
What I am trying to do is calculate race competitors place every lap as
the
times are entered. ie. first time entered for the lap is 1st, 2nd time is
2nd
etc. I only have 100 competitors per worksheet so the code below works for
me
but i just wondering if there was an easy way to select the whole column.

If Target.Column = 11 And (Target.Column Mod 2) = 1 Then
Target.Offset(0, 1) = WorksheetFunction.Max(Range(Cells(5,
Target.Offset(0, 1).Column), Cells(104, Target.Offset(0, 1).Column))) + 1

One thing you might be able to explain for me is the If not ......is
nothing
then statement. Sounds like a double negative to me and I don't understand
the logic.

I'm sure your code will do the job but in my instance I had less code and
it
worked so I haven't used yours. Thanks


"Gary Keramidas" wrote:

not sure what you're trying to do, but give this a try

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngfound As Range
If Target.Count = 1 Then
With Columns(Target.Offset(, 1).Column)
Set rngfound =
..Find(WorksheetFunction.Max(Columns(.Column)), _
lookat:=xlWhole, LookIn:=xlValues)
If Not rngfound Is Nothing Then
Range(rngfound.Address).Value = _
Range(rngfound.Address).Value + 1
End If
End With
End If
End Sub


--

Gary Keramidas
Excel 2003


"NDBC" wrote in message
...
I have the following code which is trying to determine the maximum value
in
the whole column beside the target cell, then add 1 to it and enter it
in
the
column beside the target cell. It doesn't give an error but it is not
selecting the right values

Target.Offset(0, 1) = WorksheetFunction.Max(Range(Target.Column + 1 &
":"
&
Target.Column + 1)) + 1

Thanks