Type Mismatch trying to find cell value

[sharky12345]

I'm using this to try and identify which row a cell values appears on another sheet;

Dim YourVariable As VariantDim rowCount As Integer
Set YourVariable = Sheet21.Range("B2").Value
With ActiveSheet.Range("A:A")
    Set uRng = .Find(YourVariable, , xlValues, xlWhole, , MatchCase:=False, searchformat:=False)
    If Not uRng Is Nothing Then
        uRng.Activate
        rowCount = ActiveCell.Row
    End If
End With

I am getting a Type Mismatch Error 13 on this line if anyone knows why;

Set YourVariable = Sheet21.Range("B2").Value

For info, the cell B21 contains a lot of text and some numbers.

 

[mumps]

Try:

YourVariable = Sheet21.Range("B2").Value

 

[Jon von der Heyden]

Hi

You are using Set YourVariable...

Set is for Objects but you are after the Value property. Either lose the Set or replace with Let.

 

[MickG]

Remove the:- .Value

set YourVariable = Sheet21.Range("B2").Value

 

[sharky12345]

Guys,

Thanks for your suggestions - I have amended my code as follows;

Dim YourVariable As VariantDim rowCount As Integer
YourVariable = Sheet21.Range("B21").Value
 
With Sheet28.Range("A2:A267")
Set uRng = .Find(YourVariable, , xlValues, xlWhole, , MatchCase:=False, searchformat:=False)
If Not uRng Is Nothing Then
uRng.Activate
rowCount = ActiveCell.Row


MsgBox rowCount
End If
End With

However I am still getting a Type Mismatch.......sorry if I have missed something obvious?