Can this be simplified?

Hi everybody,

I made a small VB procedure in Excel. It controls a list of 12 cells
containing numbers. It sees to it that no duplicate numbers can be in the
list: if the user inserts a number that is already there, the previous
number is reased.

The procedure works ok. But I am sure that it can be made a little more
simple, for instance by using a FOR loop or perhaps a FOR EACH loop.
Does anybody know how?

This is the listing:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Application.EnableEvents = False
v = Target.Value
r = Target.Row
If Range("c8").Row <> r And Range("c8").Value = v Then Range("c8").Value
= ""
If Range("c9").Row <> r And Range("c9").Value = v Then Range("c9").Value
= ""
If Range("c10").Row <> r And Range("c10").Value = v Then
Range("c10").Value = ""
If Range("c11").Row <> r And Range("c11").Value = v Then
Range("c11").Value = ""
If Range("c12").Row <> r And Range("c12").Value = v Then
Range("c12").Value = ""
If Range("c13").Row <> r And Range("c13").Value = v Then
Range("c13").Value = ""
If Range("c14").Row <> r And Range("c14").Value = v Then
Range("c14").Value = ""
If Range("c15").Row <> r And Range("c15").Value = v Then
Range("c15").Value = ""
If Range("c16").Row <> r And Range("c16").Value = v Then
Range("c16").Value = ""
If Range("c17").Row <> r And Range("c17").Value = v Then
Range("c17").Value = ""
If Range("c18").Row <> r And Range("c18").Value = v Then
Range("c18").Value = ""
If Range("c19").Row <> r And Range("c19").Value = v Then
Range("c19").Value = ""
Application.EnableEvents = True
End If
End Sub

Thanks!

Niko

Why don't you use this Niko
http://www.cpearson.com/excel/NoDupEntry.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Niko" <nroorda@planet.nl> wrote in message news:d7ur3f$arr$1@reader08.wxs.nl...
> Hi everybody,
>
> I made a small VB procedure in Excel. It controls a list of 12 cells containing numbers. It sees to it that no duplicate numbers
> can be in the list: if the user inserts a number that is already there, the previous number is reased.
>
> The procedure works ok. But I am sure that it can be made a little more simple, for instance by using a FOR loop or perhaps a FOR
> EACH loop.
> Does anybody know how?
>
> This is the listing:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count = 1 Then
> Application.EnableEvents = False
> v = Target.Value
> r = Target.Row
> If Range("c8").Row <> r And Range("c8").Value = v Then Range("c8").Value = ""
> If Range("c9").Row <> r And Range("c9").Value = v Then Range("c9").Value = ""
> If Range("c10").Row <> r And Range("c10").Value = v Then Range("c10").Value = ""
> If Range("c11").Row <> r And Range("c11").Value = v Then Range("c11").Value = ""
> If Range("c12").Row <> r And Range("c12").Value = v Then Range("c12").Value = ""
> If Range("c13").Row <> r And Range("c13").Value = v Then Range("c13").Value = ""
> If Range("c14").Row <> r And Range("c14").Value = v Then Range("c14").Value = ""
> If Range("c15").Row <> r And Range("c15").Value = v Then Range("c15").Value = ""
> If Range("c16").Row <> r And Range("c16").Value = v Then Range("c16").Value = ""
> If Range("c17").Row <> r And Range("c17").Value = v Then Range("c17").Value = ""
> If Range("c18").Row <> r And Range("c18").Value = v Then Range("c18").Value = ""
> If Range("c19").Row <> r And Range("c19").Value = v Then Range("c19").Value = ""
> Application.EnableEvents = True
> End If
> End Sub
>
> Thanks!
>
> Niko
>

Thank you, that is very interesting. I did not know these methods.
Validation is more flexible than I thought!
However, for my target group I think it is preferable that they are able to
insert a value that has already been used. If they do, the former value
simply disappears. This cannot be done (i think) using validation. My
procedure does it.

So perhaps you (or someone else) know a way to just simplify my procedure?

Niko


"Ron de Bruin" <rondebruin@kabelfoon.nl> schreef in bericht
news:ODmNlicaFHA.3808@TK2MSFTNGP14.phx.gbl...
> Why don't you use this Niko
> http://www.cpearson.com/excel/NoDupEntry.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>

Niko,

I haven't been up long, but I think this does
what you want...
Jim Cone
San Francisco, USA

'----------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
Dim V As Variant
Dim r As Long
Dim n As Long

If Target.Count = 1 Then
Application.EnableEvents = False
V = Target.Value
r = Target.Row
For n = 8 To 19
If n <> r And Cells(n, 3).Value = V Then
Cells(n, 3).Value = ""
End If
Application.EnableEvents = True
Next 'n
End If
Exit Sub

Err_Handler:
Application.EnableEvents = True
End Sub
'-----------------------

"Niko" <nroorda@planet.nl> wrote in message
news:d7ur3f$arr$1@reader08.wxs.nl...
Hi everybody,

I made a small VB procedure in Excel. It controls a list of 12 cells
containing numbers. It sees to it that no duplicate numbers can be in the
list: if the user inserts a number that is already there, the previous
number is reased.
The procedure works ok. But I am sure that it can be made a little more
simple, for instance by using a FOR loop or perhaps a FOR EACH loop.
Does anybody know how?
This is the listing:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Application.EnableEvents = False
v = Target.Value
r = Target.Row
If Range("c8").Row <> r And Range("c8").Value = v Then Range("c8").Value
= ""
If Range("c9").Row <> r And Range("c9").Value = v Then Range("c9").Value
= ""
If Range("c10").Row <> r And Range("c10").Value = v Then
Range("c10").Value = ""
If Range("c11").Row <> r And Range("c11").Value = v Then
Range("c11").Value = ""
If Range("c12").Row <> r And Range("c12").Value = v Then
Range("c12").Value = ""
If Range("c13").Row <> r And Range("c13").Value = v Then
Range("c13").Value = ""
If Range("c14").Row <> r And Range("c14").Value = v Then
Range("c14").Value = ""
If Range("c15").Row <> r And Range("c15").Value = v Then
Range("c15").Value = ""
If Range("c16").Row <> r And Range("c16").Value = v Then
Range("c16").Value = ""
If Range("c17").Row <> r And Range("c17").Value = v Then
Range("c17").Value = ""
If Range("c18").Row <> r And Range("c18").Value = v Then
Range("c18").Value = ""
If Range("c19").Row <> r And Range("c19").Value = v Then
Range("c19").Value = ""
Application.EnableEvents = True
End If
End Sub

Thanks!
Niko

Niko,

Correction...
I have now been up a little longer.

The line "Application.EnableEvents = True" should be
moved down two lines, so it is just below "Next 'N".

Jim Cone


"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:%23MAVD2daFHA.2496@TK2MSFTNGP14.phx.gbl...
Niko,
I haven't been up long, but I think this does
what you want...
Jim Cone
San Francisco, USA
'----------------------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
Dim V As Variant
Dim r As Long
Dim n As Long
If Target.Count = 1 Then
Application.EnableEvents = False
V = Target.Value
r = Target.Row
For n = 8 To 19
If n <> r And Cells(n, 3).Value = V Then
Cells(n, 3).Value = ""
End If
Next 'n
Application.EnableEvents = True ' Correct location
End If
Exit Sub
Err_Handler:
Application.EnableEvents = True
End Sub
'-----------------------

Thank you! This is most helpful.

Niko

"Jim Cone" <jim.coneXXX@rcn.comXXX> schreef in bericht
news:%23ckt0KeaFHA.3620@TK2MSFTNGP09.phx.gbl...
> Niko,
>
> Correction...
> I have now been up a little longer.
>
> The line "Application.EnableEvents = True" should be
> moved down two lines, so it is just below "Next 'N".
>
> Jim Cone
>
>
> "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
> news:%23MAVD2daFHA.2496@TK2MSFTNGP14.phx.gbl...
> Niko,
> I haven't been up long, but I think this does
> what you want...
> Jim Cone
> San Francisco, USA
> '----------------------
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Err_Handler
> Dim V As Variant
> Dim r As Long
> Dim n As Long
> If Target.Count = 1 Then
> Application.EnableEvents = False
> V = Target.Value
> r = Target.Row
> For n = 8 To 19
> If n <> r And Cells(n, 3).Value = V Then
> Cells(n, 3).Value = ""
> End If
> Next 'n
> Application.EnableEvents = True ' Correct location
> End If
> Exit Sub
> Err_Handler:
> Application.EnableEvents = True
> End Sub
> '-----------------------

Goes into infinite recursion because the change you are making fires the
change event...


Here is a safety-catch:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
Dim V As Variant
Dim r As Long
Dim n As Long
Static MakingAChange As Boolean

If MakingAChange Then
MakingAChange = False
Exit Sub
End If
If Target.Count = 1 Then
Application.EnableEvents = False
V = Target.Value
r = Target.Row
For n = 8 To 19
If n <> r And Cells(n, 3).Value = V Then
MakingAChange = True
Cells(n, 3).Value = ""
MakingAChange = False
End If
Application.EnableEvents = True
Next n
End If
Exit Sub



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:%23MAVD2daFHA.2496@TK2MSFTNGP14.phx.gbl...
> Niko,
>
> I haven't been up long, but I think this does
> what you want...
> Jim Cone
> San Francisco, USA
>
> '----------------------
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Err_Handler
> Dim V As Variant
> Dim r As Long
> Dim n As Long
>
> If Target.Count = 1 Then
> Application.EnableEvents = False
> V = Target.Value
> r = Target.Row
> For n = 8 To 19
> If n <> r And Cells(n, 3).Value = V Then
> Cells(n, 3).Value = ""
> End If
> Application.EnableEvents = True
> Next 'n
> End If
> Exit Sub
>
> Err_Handler:
> Application.EnableEvents = True
> End Sub
> '-----------------------
>
> "Niko" <nroorda@planet.nl> wrote in message
> news:d7ur3f$arr$1@reader08.wxs.nl...
> Hi everybody,
>
> I made a small VB procedure in Excel. It controls a list of 12 cells
> containing numbers. It sees to it that no duplicate numbers can be in the
> list: if the user inserts a number that is already there, the previous
> number is reased.
> The procedure works ok. But I am sure that it can be made a little more
> simple, for instance by using a FOR loop or perhaps a FOR EACH loop.
> Does anybody know how?
> This is the listing:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count = 1 Then
> Application.EnableEvents = False
> v = Target.Value
> r = Target.Row
> If Range("c8").Row <> r And Range("c8").Value = v Then
> Range("c8").Value
> = ""
> If Range("c9").Row <> r And Range("c9").Value = v Then
> Range("c9").Value
> = ""
> If Range("c10").Row <> r And Range("c10").Value = v Then
> Range("c10").Value = ""
> If Range("c11").Row <> r And Range("c11").Value = v Then
> Range("c11").Value = ""
> If Range("c12").Row <> r And Range("c12").Value = v Then
> Range("c12").Value = ""
> If Range("c13").Row <> r And Range("c13").Value = v Then
> Range("c13").Value = ""
> If Range("c14").Row <> r And Range("c14").Value = v Then
> Range("c14").Value = ""
> If Range("c15").Row <> r And Range("c15").Value = v Then
> Range("c15").Value = ""
> If Range("c16").Row <> r And Range("c16").Value = v Then
> Range("c16").Value = ""
> If Range("c17").Row <> r And Range("c17").Value = v Then
> Range("c17").Value = ""
> If Range("c18").Row <> r And Range("c18").Value = v Then
> Range("c18").Value = ""
> If Range("c19").Row <> r And Range("c19").Value = v Then
> Range("c19").Value = ""
> Application.EnableEvents = True
> End If
> End Sub
>
> Thanks!
> Niko
>
>

oh yeah - ok, nevermind the recursion issue and my fix, you pointed out the
real problem!


"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:%23ckt0KeaFHA.3620@TK2MSFTNGP09.phx.gbl...
> Niko,
>
> Correction...
> I have now been up a little longer.
>
> The line "Application.EnableEvents = True" should be
> moved down two lines, so it is just below "Next 'N".
>
> Jim Cone
>
>
> "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
> news:%23MAVD2daFHA.2496@TK2MSFTNGP14.phx.gbl...
> Niko,
> I haven't been up long, but I think this does
> what you want...
> Jim Cone
> San Francisco, USA
> '----------------------
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Err_Handler
> Dim V As Variant
> Dim r As Long
> Dim n As Long
> If Target.Count = 1 Then
> Application.EnableEvents = False
> V = Target.Value
> r = Target.Row
> For n = 8 To 19
> If n <> r And Cells(n, 3).Value = V Then
> Cells(n, 3).Value = ""
> End If
> Next 'n
> Application.EnableEvents = True ' Correct location
> End If
> Exit Sub
> Err_Handler:
> Application.EnableEvents = True
> End Sub
> '-----------------------

Always like to try and simplify code if possible.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long, x As Variant
On Error GoTo end1
If Intersect(Target, [C8:C19]) Is Nothing Or Evaluate _
([Sum(if(C8:C19<>"",1/countif(C8:C19,C8:C19)))]) _
= [Count(C8:C19)] Then End
Application.EnableEvents = False
x = Target.Cells(1).Value
For i = 8 To 19
If Cells(i, 3).Value = x And i _
<> Target.Row Then Cells(i, 3).Value = ""
Next i
end1:
Application.EnableEvents = True
End Sub


Regards
Robert McCurdy

"Niko" <nroorda@planet.nl> wrote in message news:d7usa6$c66$1@reader08.wxs.nl...
Thank you, that is very interesting. I did not know these methods.
Validation is more flexible than I thought!
However, for my target group I think it is preferable that they are able to
insert a value that has already been used. If they do, the former value
simply disappears. This cannot be done (i think) using validation. My
procedure does it.

So perhaps you (or someone else) know a way to just simplify my procedure?

Niko


"Ron de Bruin" <rondebruin@kabelfoon.nl> schreef in bericht
news:ODmNlicaFHA.3808@TK2MSFTNGP14.phx.gbl...
> Why don't you use this Niko
> http://www.cpearson.com/excel/NoDupEntry.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>