Unique Macro

  • Thread starter Materialised[Work]
  • Start date
M

Materialised[Work]

Hi All

I am totally new to excel programming, having never needed to use it
before.
I do however have some experience with visual basic.

I have a really simple spreadsheet, which basically consists of 5
columns. It is used to record what items our A&P team has send to
validation.

Columns are laid out as such:
Description Item-No Inv-Cost PO-Cost Date

What I want to do, is impliment some sort of way, so that the same
item number can't be entered in the Item-No column more than once.

Would it be suitable to use a macro to do this? As I know it would
involve looping around the existing data checking for a match. Or
would it be better to impliment it as a database function?

Thanks in advance, and sorry if this isnt detailed enough, I'm rushing
it through to get to my coffee break.

Thanks
Mick
 
A

Arvi Laanemets

Hi

No need for code here - you can use data validation for this.

When your item numbers start from B2, then select the range p.e. B2:B10000,
select from menu Data.Validation, set Allow to Custom and enter the custom
formula
=(COUNTIF($B$2:$B2,$B2)<2)

It's all you need, but you can also edit input message and/or error alert,
when you want.
 
D

Don Guillett

As Arvi said, data validation is best but since you asked, you could
right click sheet tab>view code>insert this>save.
Now cells below row 1 in col B will be protected.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 2 And Target.Column <> 2 Then Exit Sub
If Application.CountIf(Columns(Target.Column), _
Target) > 1 Then MsgBox "No": Application.Undo
End Sub
 
Top