Macro to find user inputted text in a worksheet

[Cozkincaud]

Hi

I need help with what should be a simple macro.

I need to search a column of book/article titles to find a book/article title that I paste into an Excel Find box.

I created a macro from some Internet posts, but get the error, “Run-time error ‘91’: Object variable or With block variable not set”.

This is the macro:

Sub CheckIfOneTabFilesDownloaded()
' Macro to check if Internet tabs sent to One Tab have already been downloaded
Dim Title As String
Sheets("Downloads - November 18, 2023").Select
ActiveSheet.Range("A1:A1").Select
Title = InputBox("Enter Title")
Dim fColumn As Range

Set Rng = Find(What:=Title, LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False, After:=fColumn("A2"))

If fColumn Is Nothing Then
MsgBox "Nothing found"
Else:
fColumn.Activate
End If
End Sub

with the error line being the “Set Rng . . . “ statement.

My understanding is I am attempting to use an object variable that has yet to reference a valid object. The error is in the "Set Rng' statement. I assume the following lines are fine.

After an Internet search I added “Cells.Find” to my “Set Rng” statement but to no avail.

So I would be extremely grateful for whatever help and suggestions forum members can give.

My thanks in advance for whatever help and advice

 

[kevin9999]

I need to search a column of book/article titles to find a book/article title that I paste into an Excel Find box.

I don't fully understand that part, but is there any reason why you don't just highlight column A and press Ctrl+F?

 

[Cozkincaud]

Because, as you might see from my macro, I have to:

1. Switch from one worksheet to another
2. Go to the cell, A1
3. Hit control F

I want to cut out these steps. I should probably add a statement returning me to the oriiginal worksheet if the title is not found

Thanks for your comment.

 

[kevin9999]

See if this does what you wanted:

Option Explicit
Sub FindTitle()
    Dim title As String, rng As Range
    title = InputBox("Enter Title")
    Set rng = Worksheets("Downloads - November 18, 2023").Range("A:A").Find(title, LookAt:=xlPart)
    
    If Not rng Is Nothing Then
        Application.Goto rng, scroll:=True
    Else
        MsgBox title & " could not be found"
    End If
End Sub

 

[Cozkincaud]

It works!

Many thanks for your help.

 

[kevin9999]

Happy to help & thanks for the feedback