Code runs slow about 4 seconds

[ZZD]

This code runs very slow. Is there any way to speed it up? I think it is because the program must go back and forth between the cells in the spreadsheet and the program?

I have attached a section of code that checks to see if the number entered by a user is present in a Range of cells. If the number is present it is reset to "". This prevents duplicate entries of the same number in the Range. The Target cell is then reset to the original number.


' This loop clears any cells that have the same machine number as the one entered by the user
If 50 < Machine Then
If 76 > Machine Then

For Each c In Range("K22:S31").Cells
Application.EnableEvents = False
If c.Value = Machine Then c.Value = ""
Application.EnableEvents = True
Next
'This statement puts the machine number back in the target cell as the last loop just erased it.
Target.Value = Machine
End If
End If

I can include the rest of the program but this is my first post and I wasn't sure how much detail to put in here.

Thanks for your help
Ric

 

[SkipVought]

There are better ways to prevent a user from entering a duplicate value, than looping.

Check out Data > Validation using a Custom formula like...

[tt]
=COUNTIF($A$1:A1,A1)=1
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ZZD]

Thanks for the reply
but:
I am not trying to prevent them from entering a number What I am trying to do is erase the number if it is listed somewhere else in the Range.

Each row of the range is assigned to a different person to look after.

The numbers entered by the user (supervisor) represent the machines the person assigned to that row of the Range is responsible for.

Thanks again
Ric

 

[SkipVought]

use Data > Filter > Advanced filter - UNIQUE values or use MS Query to return DISTINCT values.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}