Search code examples
excelvbatypesmismatch

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

Solution

  • 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.