Can this be simplified?

N

Niko

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
 
N

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
 
J

Jim Cone

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
'-----------------------

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
 
J

Jim Cone

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


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
'-----------------------
 
W

William Benson

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
 
W

William Benson

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

Robert McCurdy

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF argument limit - how convert formula to VBA? 2
Loop is slow trying array code 17
Code to Implement Required Cells Not Working 3
type mismatch error in macro in excel 0
Merge Append all sheet 1
Resize a password protected page 2
Column is overwritten 4
Smaller VBA? Loop or Offset? 3

Top