Vc precisa usar uma validação para impedir a inclusão do CPF já existente...
Outra opção é usar um procv q vc já tentou, ou um corresp, buscando o CPF digitado na célula ao lado olhando pras células acima dela..
Coloque um modelo compactado q eu monto pra vc...

Boa tarde!!

Um exemplo para que você possa tentar adaptar.

Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("MinhaGuia")
    
     'Encontra a 1º linha vazia na Base de Dados
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
     
    If WorksheetFunction.CountIf(ws.Range("A2", ws.Cells(iRow, 1)), Me.txtPart.Value) > 0 Then
        MsgBox "Duplicidade encontrada", vbCritical
        Exit Sub
    End If
     
     
     'Verifica a entrada de numero
    If Trim(Me.txtPart.Value) = "" Then
        Me.txtPart.SetFocus
        MsgBox "Por favor digite o código"
        Exit Sub
    End If
     
     'Copia os dados para a Base de Dados
    ws.Cells(iRow, 1).Value = Me.txtPart.Value
    ws.Cells(iRow, 2).Value = Me.txtLoc.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtQty.Value
     
     'Limpa os dados
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtDate.Value = ""
    Me.txtQty.Value = ""
    Me.txtPart.SetFocus
     
End Sub

Att

planilha em anexo!

Nem olhei pro formulário, pq ele tá longe de ficar completo ...

Usei:
- formatar como tabela, para garantir as fórmulas e formataç~ies e validações para todas as linhas, sempre...
- validação de dados para impedir a entrada de cpf repetido
- formatação condicional, para caso vc escreva um CPF via VBA (sim o VBA ignora a validação), ele fique pintado de vermelho, a partir do segundo digitado.

Incluí uma coluna de data, pois vc mencionou que queria ver a data do cadastro.... o recurso de tabela permitirá filtrar o cpf repetido, e assim vc verá a(s) data(s) em que ele foi cadastrado...

Segue o modelo...

criei o código, porem não estou conseguindo montar a validação.

Boa tarde!!

Não conseguiu adaptar?
Tente se basear na ideia abaixo.

If WorksheetFunction.CountIf(ws.Range("A2", ws.Cells(iRow, 1)), Me.txtPart.Value) > 0 Then
        MsgBox "Duplicidade encontrada", vbCritical
        Exit Sub
    End If

Att

Experimente:

Dim rowcount As Long
Dim clt As Control
rowcount = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If VBA.IsNumeric(Application.Match(Bcpf.Text, Sheets("plan1").Range("F1:f" & rowcount), 0)) Then
    MsgBox "CPF já cadastrado dia - " & Cells(Application.Match(Bcpf.Text, Sheets("plan1").Range("F1:f" & rowcount), 0), "H")
    Exit Sub
End If
With Worksheets("Plan1").Range("a3")
    .Offset(rowcount, 0).Value = Me.Bnome.Value
    .Offset(rowcount, 1).Value = Me.BSbrNome
    .Offset(rowcount, 2).Value = Me.Bendr.Value
    .Offset(rowcount, 3).Value = Me.Bcidade.Value
    .Offset(rowcount, 4).Value = Me.Btel.Value
    .Offset(rowcount, 5).Value = Me.Bcpf.Value
    .Offset(rowcount, 6).Value = Me.Brg.Value
    .Offset(rowcount, 7).Value = Format(Now, "dd/mm/yyyy")  
End With

Muito obrigado, Resolvido! Esta Funcionando corretamente!