Userform Text Box Duplicates

[Aviles]

Hello,
I'm currently using the below code in a userform:

Sheets("Database").Visible = True
    Sheets("Database").Activate
    NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(NextRow, 1) = txtClientFM.Text
    Cells(NextRow, 2) = txtNewPortfolio.Text

...which simply takes the values from the 2 text boxes and places them in the "Database" sheet.

It works fine, but I would like to add to this some code which avoids the user from entering a Portfolio number (in txtNewPortfolio) that already exists in the "Database" sheet... so avoid enetering duplicates. The list of existing Porfolios are on Column B of the "Database" sheet.

If they enter an existing Portfolio, then: MsgBox "This Porfolio code already exists."

Thanks.

 

[Norie]

You could use CountIf to check for duplicates.

Dim rngPFolios As Range
Dim LastRow As Long
 
With Worksheets("Database")
 
      LastRow = .Range("B" & Rows.Count).End(xlUp).Row
 
      Set rngPFolios = .Range("B1:B" & LastRow)
 
End With        
 
NoFolios = Application.WorksheetFunction.CountIf(rngPFolios, txtNewPortFolio)
 
If NoFolios>0 Then
 
     Msgbox "This Porfolio code already exists."
 
Else
    Worksheets("Database").Cells(LastRow+1, 1) = txtClientFM.Text
    Worksheets("Database").Cells(LastRow+1, 2) = txtNewPortFolio.Text
End If

 

[Aviles]

Thanks Norie, that worked great!