Find Value type mismatch

[baha17]

Hi Everybody,

I tried to find value on the other workbook but I am having a kind of trouble. Gives me error "Type mismatch"
Can anyone help me?Below is the code in my VBA project
Thanks in advance and happy weekend.
Baha

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim ResVal As Variant
Dim FindId As Range
If Range("Cnst") <> 1 Or Target.Row = 1 Then Exit Sub
If Target.Column = 4 Or Target.Column = 8 Or _
Target.Column = 12 Or Target.Column = 16 Then
'On Error GoTo 100
Target.Offset(0, 1) = _
Application.VLookup(Target.Value, Sheets("Staff").Range("A2:D3000"), 2, 0)
If IsError(Application.VLookup(Target.Value, Sheets("Staff").Range("A2:D3000"), 2, 0)) Then
Target.Offset(0, 1) = "NoName"
End If

Cells(Target.Row, 1).Select
AlterOneRecord
ShowingAll
TransferTableFromAccess
Sheets("Copy").Columns("S:S").ShrinkToFit = True
MakeUp
FilteringPit
'If Application.WorksheetFunction.CountIf(Range("D:D"), Target.Value) > 1 _
'And Target.Value <> 0 Then
If Trim(Target.Value) <> "" Then
With Sheets("Staff").Range("A:A")
Set FindId = .Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
If Not FindId Is Nothing Then
MsgBox "TM already on " & FindId.Offset(0, 2).Value, , "TM is assigned on another location"
Cells(Target.Row, 1).Select
End If
End With
End If
'End If
End If
Exit Sub
100 Target.Offset(0, 1) = "NoName"
Cells(Target.Row, 1).Select
AlterOneRecord
ShowingAll
TransferTableFromAccess
Sheets("Copy").Columns("S:S").ShrinkToFit = True
MakeUp
FilteringPit
Cells(Target.Row, 1).Select
Application.ScreenUpdating = True
End Sub

 

[baha17]

I am sorry to answer my own thread but i am kind of stuck in this code.
Any help is greatly appreaciated.
Baha

 

[Firefly2012]

On which line do you get the error?

Also, what value is being looked up at the time the error is received?

 

[baha17]

Hi Firefly,

Set FindId = .Find(What:=Target.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ </pre> MatchCase:=False, SearchFormat:=False)

That is the line I get error. What I wanted is whenever user enter the value on Column D, I want to check weather there is any double entry or not.If so I just want to know offset(0,2).value of the double entry.I could not understand what I am missing?
Thanks for the help
Baha

 

[Firefly2012]

Try changing the line to:

Set FindId = .Find(What:=Target.Cells(1,1).Value, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

 

[baha17]

thank you Firefly.I am not very sure about target.cells(1,1)
but i will try tomorrow.
thank u for your answer.i will put my result tomorrow.

 

[baha17]

Hi Firefly,
It worked as a charming Thank you very much for the help.
Have a good day.
Baha

 

[Firefly2012]

You're welcome

Using Target.Cells(1,1).Value worked because your Target was (at least when it failed) a multi-cell range - my amendment specified only a single cell value to be taken