another simple thing probably

[chris9104]

Hi guys, the below code should search my range for an existing value a return a msgbox if it does

'Check Record Does Not Already Exist
If Application.CountIf(Range("Staff Numbers"), txtstaffno.Value) > 0 Then
MsgBox "Staff Number Already Exists.", vbExclamation, "Error"
Me.txtstaffno.SetFocus
Exit Sub
End If

However I just cant get it to work, please help

 

[chris9104]

never mind, got it to work

 

[Willem!]

what was the problem? how did you fix it ?

 

[Georgiboy]

Two things i would look at are...

in this part,
If Application.CountIf(Range("Staff Numbers"), txtstaffno.Value) > 0 Then
this bit txtstaffno.Value should probably have Me. in front of it.
like,
If Application.CountIf(Range("Staff Numbers"), Me.txtstaffno.Value) > 0 Then

Also

in this part,
If Application.CountIf(Range("Staff Numbers"), txtstaffno.Value) > 0 Then
this bit Range("Staff Numbers") should probably have a sheet name in front of it.
like,
If Application.CountIf(Sheets("Sheet1").Range("Staff Numbers"), Me.txtstaffno.Value) > 0 Then

Other than that i can see no reason why it would not work.

 

[chris9104]

Yeah I did end up using the same method as above so ended up with:

'Check Record Does Not Already Exist
If Application.CountIf(Sheets("Staff Data").Range("StaffNumbers"), Me.txtstaffno.Value) > 0 Then
MsgBox "Staff Number Already Exists.", vbExclamation, "Error"
Me.txtstaffno.SetFocus
Exit Sub
End If

then also realised could do it this way:

'Check Record Does Not Already Exist<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If WorksheetFunction.CountIf(Worksheets("Staff Data").Range("StaffNumbers"),Me.txtstaffno.Value) > 0 Then<o></o>
MsgBox "Staff Number Already Exists.", vbExclamation, "Error"<o></o>
Me.txtstaffno.SetFocus<o></o>
Exit Sub<o></o>
End If

Thanks for the replies

 

[Willem!]

Glad you got it working!