CODEWITHSUNDEEP
excelvbatypesmismatch
Claire Gomes
Claire Gomes

Reputation: 13

Type Mismatch error when using range.find method

Could anyone help me with using the range.find method. The type mismatch error keeps coming up and I'm not sure why.

I've tried setting the range variable to a Range, then using Set to define it and I've also tried without using Set, but then it comes up with another error.

Dim r1 As Range
    Set r1 = Range("B:K").Find("WhatToFind").row

I expect the above code to give me the row of the WhatToFind, and it is definitely in the worksheet, but still gives the error.

Even if I remove the .Row from the end, I get this error:

Run-time error '1004':
Application or Object defined error

Upvotes: 1

Views: 982

Answers (5)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

You need to check that something is found before you can return anything from it. 

Sub Test()

    Dim r1 As Range
    Dim lFoundRow As Long
    Set r1 = Range("B:K").Find("WhatToFind")
    If Not r1 Is Nothing Then
        MsgBox "WhatToFind is on row " & r1.Row
        lFoundRow = r1.Row
    Else
        MsgBox "Not found"
    End If

End Sub

I doubt this is the cause of the error, but FIND remembers the settings from the last time it was used (either through code or manually on the worksheet with Ctrl+F).

You can set each parameter on the FIND: 

Sub Test()

    Dim r1 As Range
    Dim lRow As Long

    With ThisWorkbook.Worksheets("Sheet1").Range("B:K")
        Set r1 = .Find( _
            What:="WhatToFind", _
            After:=.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            MatchByte:=False, _
            SearchFormat:=False)

        If Not r1 Is Nothing Then
            lRow = r1.Row
        Else
            MsgBox "Not found"
        End If
    End With

End Sub

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 55073

Add-in?

There is no Worksheet. You are running this code from e.g. an Add-in or you are trying to run this code on an chart sheet. You have to select a Worksheet in the tab.

Upvotes: 0

Chronocidal
Chronocidal

Reputation: 8081

Try fully qualifying your range (e.g. Set r1 = Sheet1.Range("B:K").Find("WhatToFind"))

If you leave it unqualified, it will try to use the ActiveSheet. If you are, for example, looking at a Chartsheet instead of a Worksheet, then this will throw a 1004 error - because the ActiveSheet, being a chart, doesn't have any cells, columns, or Ranges to search.

Upvotes: 1

Hambone
Hambone

Reputation: 16407

The Row Property is a type long, and it represents the row number, not the row (Range) object itself.

If you want the row object, you probabaly want EntireRow.

Dim r1 As Range
Dim r2 as Long
Set r1 = Range("B:K").Find("WhatToFind").EntireRow
r2 = Range("B:K").Find("WhatToFind").Row

And just to clarify, although you might already know this, set is only used for reference types. Since a long is a value type, you do not use the set command.

Upvotes: 1

Alexey C
Alexey C

Reputation: 172

Your r1 is a range type, but

Set r1 = Range("B:K").Find("WhatToFind").row - is not a Range. It's number.

Use

Set r1 = Range("B:K").Find("WhatToFind")
dim r1row
r1row = r1.row

Or 

dim lR as long
lR = Range("B:K").Find("WhatToFind").Row

But remember, that Range("B:K").Find("WhatToFind") can be "Nothing"

Upvotes: 1

Related Questions