Search for existing values in woorksheet!?
 

Hi all,

i need some help coming up with a code.

I have a Userform in wich users add new data to a database.
When a user enters a new post he/she needs to enter a number for
example 6501. I need a code that search the entire column ( A ) for
just this number, and if it already exist a MsgBox pops up and inform
the user!

Grateful for all help i can get!

Search for existing values in woorksheet!?
 

with Worksheets("Sheet1").Columns("A:A")
set oCell = .Find (Textbox1.Text)
If not oCell Is Nothing Then
Msgbox "Value already exists"
End If
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Daniel" wrote in message
m...
Hi all,

i need some help coming up with a code.

I have a Userform in wich users add new data to a database.
When a user enters a new post he/she needs to enter a number for
example 6501. I need a code that search the entire column ( A ) for
just this number, and if it already exist a MsgBox pops up and inform
the user!

Grateful for all help i can get!

Search for existing values in woorksheet!?
 

Daniel

You could use a function similar to below

Function CheckValues(lInputNo As Integer) As Boolean
Dim lLastRow As Long
lLastRow = Range("A65536").End(xlUp).Row
If Not Range("A1:A" & lLastRow).Find(lInputNo) Is Nothing Then
CheckValues = True
Exit Function
End If
CheckValues = False
End Function

The lInputNo parameter is the entry from your user form. This then finds
the last row currently in column A and uses the find method of the range
object to see if the number exists in this range. If it doesn't the method
returns NOTHING and CheckValue=False and you should show no msgbox. If it
finds a duplicate it returns true and you should show a message box. My
test sub may help to explain the inputting of the parameter too

Sub Test()
Dim x As Boolean
x = CheckValues(InputBox("Enter a number", "Test"))
If x = True Then
MsgBox "Duplicate number", vbOKCancel
End If
End Sub
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Daniel" wrote in message
m...
Hi all,

i need some help coming up with a code.

I have a Userform in wich users add new data to a database.
When a user enters a new post he/she needs to enter a number for
example 6501. I need a code that search the entire column ( A ) for
just this number, and if it already exist a MsgBox pops up and inform
the user!

Grateful for all help i can get!

Search for existing values in woorksheet!?
 

To check for the existence of the number in column A you
can use the WorksheetFunction CountIf. I will call the
stored value of the number entered in your
UserForm 'InputNumber' and define a boolean variable
NewNumber

NewNumber = False

While Not(NewNumber)
' Put your code here to display your userform and store
the entered value in InputNumber
NewNumber = (WorksheetFunction.CountIf(Range
("A:A"),InputNumber)=0)
If Not(NewNumber) Then MsgBox "Number Already Exists!"
Wend

This loops through the input routine until you have a
number that does not already exist in column A (displaying
the messagebox if it finds the number already in column A)

Might need some tweaks to fit your code (and to deal with
null entries, error trapping, etc.), but the general idea
hopefully can do what you need to do.

K Dales

-----Original Message-----
Hi all,

i need some help coming up with a code.

I have a Userform in wich users add new data to a
database.
When a user enters a new post he/she needs to enter a
number for
example 6501. I need a code that search the entire column
( A ) for
just this number, and if it already exist a MsgBox pops
up and inform
the user!

Grateful for all help i can get!
.

Search for existing values in woorksheet!?
 

Thanx for all help, i tested them all and found them very helpful!