|
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 |
Can this be simplified?
Why don't you use this Niko
http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Niko" wrote in message ... 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 |
Can this be simplified?
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" schreef in bericht ... Why don't you use this Niko http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Ron de Bruin http://www.rondebruin.nl |
Can this be simplified?
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" wrote in message ... 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 |
Can this be simplified?
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" wrote in message ... 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 '----------------------- |
Can this be simplified?
Thank you! This is most helpful.
Niko "Jim Cone" schreef in bericht ... 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" wrote in message ... 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 '----------------------- |
Can this be simplified?
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" wrote in message ... 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" wrote in message ... 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 |
Can this be simplified?
oh yeah - ok, nevermind the recursion issue and my fix, you pointed out the
real problem! "Jim Cone" wrote in message ... 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" wrote in message ... 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 '----------------------- |
Can this be simplified?
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" wrote in message ... 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" schreef in bericht ... Why don't you use this Niko http://www.cpearson.com/excel/NoDupEntry.htm -- Regards Ron de Bruin http://www.rondebruin.nl |
| All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com