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!
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!