Compile error: Argument not optional

[Bandito1]

Hello all,

I have a textbox named txtCorrectby
I would like that the data that is filled in this textbox matches a list.
If not the user should be warned.

I got this code for it;

Private Sub txtCorrectBy_AfterUpdate()
Dim rngCell As Range
Dim bMatch As Boolean
Dim vVal
    
    vVal = txtCorrectBy.Text
    bMatch = WorksheetFunction.CountIf(Range(Sheets("PersonSearch")("B2:B153"), vVal)) > 0
         
     'If a match was found then alert the user
    If bMatch Then MsgBox ("Name already exsists.")
     
End Sub

But it gives Compile error: Argument not optional

Somebody knows why?

 

[Fluff]

Try

bMatch = WorksheetFunction.CountIf(Sheets("PersonSearch").Range("B2:B153"), vVal)) > 0

 

[Peter_SSs]

Since vba is being used, I would consider just using functions/methods etc native to that environment, rather than calling up a worksheet function.

Private Sub txtCorrectBy_AfterUpdate()
  Dim rngCell As Range
  Dim vVal As String
    
  vVal = txtCorrectBy.Text
  Set rngCell = Sheets("PersonSearch").Range("B2:B153").Find(What:=vVal, LookAt:=xlWhole, MatchCase:=False)
  
  'If a match was found then alert the user
  If Not rngCell Is Nothing Then MsgBox "Name already exists."
End Sub

 

[Bandito1]

Both work, thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.