COUNTIF in VBA

[R D S]

I have a userform which adds records to sheet 1.
One of the textboxes is called reference and when i change the contents of
this i want to make sure said contents arent already present in sheet1,col
B.

So i see then that i need to use COUNTIF.

To check if the entry in A1 appears once in col B i used
=COUNTIF(B:B,A1)=1

To check if the entry occurs more than once in the list in col B do i use
=COUNTIF(B:B,A1)>=1

And how do i crowbar this into vba?

For VBA im thinking something along the lines of
formula = "=COUNTIF(B:B,me.reference.value)>=1"

but them im lost on the message box alert.

Thanks to anyone who can show me the way.
Rick

PS apoloies that this is a repost, i really could do with sorting this
before morning.

 

[Frank Kabel]

Hi
try
with activesheet
If application.worksheetfunction.countif( _
.Range("B:B"),.Range("A1").value) > 0 then
msgbox "Exist in list"
end if
end with

 

[R D S]

Hi, the following almost works in my VBA for the reference textbox in my
userform

Private Sub Reference_Change()
With ActiveSheet
If Application.WorksheetFunction.CountIf( _
.Range("C:C"), Me.Reference.Value) = 1 Then
MsgBox "Exist in list"
End If
End With
End Sub

but the number i need to enter is seven digits with a slash eg 123/456 ut im
getting the exists in list popup as i enter the second digit.

Any ideas,
Thanks,
Rick