validating data in acolumn
 

Hi all, I've got a column of invoice numbers and each time I add one I want
to make sure it it is not duplicated. I've tried using a function with
copy/paste validation, but it doesn't always work as the invoices can be text
as well as number. Any ideas. Thanks very much.
--
delmac

validating data in acolumn
 

How about something like this? Of course you can make the inputbox a
specific cell or the activecell if you choose... that's just an example
since you didn't specify. Hope that helps.

Sub MyTest()
Dim MyStr As String
MyStr = InputBox("New Invoice:")
Columns("A:A").Select
Range("A1").Activate
On Error GoTo NoDuplicate
Selection.Find(What:=MyStr, LookAt:=xlWhole).Activate
GoTo Duplicate
Duplicate:
'Code for if duplicate is found
Exit Sub
NoDuplicate:
'Code for if NO duplicate is found
Exit Sub
End Sub


delmac Wrote:
Hi all, I've got a column of invoice numbers and each time I add one I
want
to make sure it it is not duplicated. I've tried using a function with
copy/paste validation, but it doesn't always work as the invoices can
be text
as well as number. Any ideas. Thanks very much.
--
delmac


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=542146

validating data in acolumn
 

Hi,

You could attach some code to the code module for the worksheet & trap the
worksheet change event.

Provided you have a named range called "invoicenumbers" the following may
help;

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ranCell As Excel.Range
Dim iPointer As Integer

For Each ranCell In Application.Intersect(Target,
Me.Range("invoicenumbers"))

If ranCell < "" Then

For iPointer = 0 To (ranCell.Row -
Me.Range("invoicenumbers").Cells(1, 1).Row - 1)

If Me.Range("invoicenumbers").Cells(iPointer + 1, 1) =
ranCell Then

MsgBox "Duplicates with row " & (iPointer +
Me.Range("invoicenumbers").Cells(1, 1).Row)
ranCell = ""
Exit Sub

End If

Next

End If

Next ranCell

End Sub

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"delmac" wrote:

Hi all, I've got a column of invoice numbers and each time I add one I want
to make sure it it is not duplicated. I've tried using a function with
copy/paste validation, but it doesn't always work as the invoices can be text
as well as number. Any ideas. Thanks very much.
--
delmac