Unique Macro
 

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

Unique Macro
 

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.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Materialised[Work]" wrote in message
om...
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

Unique Macro
 

As Arvi said, data validation is best but since you asked, you could
right click sheet tabview codeinsert thissave.
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


--
Don Guillett
SalesAid Software

"Materialised[Work]" wrote in message
om...
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