Visual Basic para Aplicaciones - Error en Buscarv en VBA

buenos días, estoy intentando que en una hoja cuando pongo el nombre me ponga automático el DNI, pero sin tener los datos en otra Hoja, lo he provado con este código en la celda

=SI.ERROR(BUSCARV(C5;Hoja1!$C$5:$D$2000;2;0);"")
Funciona pero da error circular, entonces he intentado hacer una macro pero en vez de ponerme los datos en la celda lo que hace es borrarlo todo.
Adjunto un excel con la prueba por si alguien puede mirar que hago mal

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D5:D100")) Is Nothing Then
fin = Application.CountA(Worksheets("PROVA").Range("A:A"))

With Worksheets("PROVA").Range("D5:D" & fin)
.Formula = "=IF(ISERROR(VLOOKUP(RC[-3],PROVA!R2C3:R100C5,4,FALSE)),"""",VLOOKUP(RC[-3],PROVA!R2C3:R100C5,4,FALSE))"
.Formula = .Value
End With
End If
End Sub

Gracias

• Prova-poner-DNI.zip(12,4 KB)

Empecemos por la fórmula:

Escribes el nombre
¿Donde?
¿En la celda C5?
Y donde lo busca:
¿En la misma celda que lo has escrito? Qué sentido tiene.


Dices que es para buscar el DNI
¿Dondé está el DNI?
Dices: sin tener los datos en otra Hoja
¿Dondé está la tabla de dos columnas con Nombre y DNI?

Y ya ni me miro la macro porque debes de estar cometiendo los mismos errores que con la fórmula.

Saludos.
\\//_

he mejorado un poco pero ahora tengo otro error , he puesto el siguiente código que copia lo que escriba en la columna c en la celda K3 y la uso como celda con el nombre que busco


If Not Intersect(Target, Range("c5:c100")) Is Nothing Then
a = ActiveCell.Address
Range(a).Copy
Range("K3").PasteSpecial xlPasteValues
Application.CutCopyMode = False

Exit Sub
He cambiado también lo que tu me dices pero el problema ahora es que el nuevo código no me deja escribir en la columna c, por mas que busco no se como solucionarlo
te adjunto la copia del excel modificado por si lo puedes mirar
Gracias

• Prova-poner-DNI.zip(16,6 KB)

Resuelto, con este código
If Target.Column = 4 Then [K3] = Cells(Target.Row, 3)
me copia lo que ponga en la siguiente celda de la colunma C en la celda K que uso como Buscar
gracias de todos modos

En tu ultimo libro en la celda L3 he puesto esta formula:


Y funciona. Si tienes dos pacos solo encuentra el primero.

Saludos.
\\//_