VBA Excel: error 1004 when using Range.Find method

Question

I am learning about the Range.Find method. I have a column of letters (a to t) in column A of Excel, from row 1 to row 20.

This code:

Public Sub MyFind3()

'Accepts an input for a fluid length search field (column A)
Dim WhatToFind As String
Dim DataRange As Range

Set DataRange = Range("A1", Range("A1").End(xlDown))
WhatToFind = InputBox("Enter text to search for:", "Find Text")
Range(DataRange).Find(WhatToFind).Select

End Sub

...works up to the last line when I get this error: "Run-time error 1004: Application-defined or object-defined Error". It does not find the Find match. Can anyone point out my error please? Many thanks.

Answer 1

Change the last line to:

DataRange.Find(WhatToFind).Select

DataRange is already a Range object, so does not need to be listed as Range(DataRange)

Answer 2

.Find() returns a range that you'll usually want to assign to a range variable. I would rework your code slightly like this:

Public Sub MyFind3()

'Accepts an input for a fluid length search field (column A)
Dim WhatToFind As String
Dim DataRange As Range

WhatToFind = InputBox("Enter text to search for:", "Find Text")
Set DataRange = Range("A1", Range("A1").End(xlDown)).Find(What:=WhatToFind)
if not DataRange is Nothing then
  DataRange.select
else
  msgbox (WhatToFind & " wasn't found")
end if

End Sub

That gives you the advantage of being able to handle the 'not found' condition, and you have a range var you can now do something else with.

Also, be aware that when you call .Find(), it will execute with whatever the last settings were, so setting more of the parameters, such as LookIn, LookAt, and MatchCase will help ensure that this search works exactly as you intend.