PDA

View Full Version : VBA Prevent Duplicate Entry via Input Box Help



mrstruggler
03-22-2012, 02:25 PM
I am trying to create a array from my column of values but want to prevent the user from entering duplicate values.

The user will enter a 'account number' i.e 123 (Column A) into an input box and I want to insert code so that before it is printed in the column it will make sure the value does not already exist either in the column or the array I have also created for the values. This is my current code for my growing dynamic array:


Option Explicit

Sub GrowingAccountNumberArray()
Dim AccountNumberArray()
Dim AccountNumberY As String
Dim CurrentIndex As Integer
Dim RowLength As Integer
Dim n As Integer

CurrentIndex = 0


ReDim Preserve AccountNumberArray(CurrentIndex)
AccountNumberArray(CurrentIndex) = InputBox("Please enter a three digit account number (i.e. 123)", "Customer Details")

CurrentIndex = CurrentIndex + 1


For n = 0 To CurrentIndex - 1

AccountNumberY = "ACC" & AccountNumberArray(n)
Next n

If AccountNumberY = "" Then
Exit Sub
Else
RowLength = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & RowLength).Value = AccountNumberY
End If

End Sub




What should I do from here to prevent duplicates??? Thanks in advance!

shrivallabha
03-22-2012, 09:44 PM
I am trying to create a array from my column of values but want to prevent the user from entering duplicate values.

The user will enter a 'account number' i.e 123 (Column A) into an input box and I want to insert code so that before it is printed in the column it will make sure the value does not already exist either in the column or the array I have also created for the values. This is my current code for my growing dynamic array:

What should I do from here to prevent duplicates??? Thanks in advance!
Welcome to VBAX.

Try it like this.
Option Explicit
Sub GrowingAccountNumberArray()
Dim sAccNum As String
Dim rAccLst As Range
Dim res

Set rAccLst = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) 'Change it to suit your requirement

Retry:
sAccNum = Application.InputBox("Please enter a three digit account number (i.e. 123)", _
"Customer Details", , , , , , 1) 'Ensure user inputs numbers only

If Not rAccLst.Find(What:=sAccNum, LookIn:=xlValues, Lookat:=xlPart) Is Nothing Then
MsgBox "Sorry, the Account Number already exists!"
GoTo Retry
Else
Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = "ACC" & sAccNum
End If

End Sub