Runtime error 91 - When using Range.Find and String from TextBox

Question

I'm facing a problem with a function I'm trying to create.

The function should do the following: - Ask for the values - Return True or False if the value exists or not in the B:B column.

This is what I have so far...

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Ret As Boolean
Dim FindText As String
If KeyCode = 13 Then
    With Sheets("Dados")
        Columns("B:B").Select
        FindText = TextBox1.Text
        Ret = Selection.Find(What:=FindText, After:=.Cells(1, 1), LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
        :=False, SearchFormat:=False).Activate
        MsgBox "Hy " & FindText & " > " & Ret
        If FindText Is Nothing Then Exit Sub
    End With
End If End Sub

This works great when the value is true, but gives me the runtime error 91 - Object variable or With block variable not set if the value is false :(

As a courtesy, you should let people know that you've asked this question on another site and already had answers... — SierraOscar, Jan 13 '15 at 15:07
(this works but I don't know if I can fully recommend this so I am just leaving it as a comment) You could just initially set `Ret` to false, then use `On Error Resume Next` then call your `.Find(...)` and then re-set the error handling back to `On Error GoTo 0` — chancea, Jan 13 '15 at 15:13
@chancea I would strongly advise against that, it's just ignoring the error rather than dealing with it appropriately. Setting a range via .Find() and testing if it is `Nothing` for example would be a cleaner option. Too many people use `On Error` statements without understanding the implications of poor error handling which inevitably leads to more problems in the future. — SierraOscar, Jan 14 '15 at 17:49
@SO Yeah I basically agree with you, that is why I would never leave that kind of suggestion as an answer. It is an ugly workaround but bad practice. But hey, if you are only making a macro to fix a spreadsheet once or if it's not going to be used in some kind of industry then I think it's (deep breath) _somewhat_ okay to do this. — chancea, Jan 14 '15 at 18:00

score 0 · Answer 1 · answered Jan 13 '15 at 14:58

FindText is a String Variable not an Object variable. Only Objects can be set to or have the Value Null. So when the False is executed. It is looking for an Object, since it is not set it throws the error.

You do not even need the line of code as it computes afters getting into the Selection. You need to validate the Data before going for checking, so try this maybe.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim Ret As Boolean
    Dim FindText As String

    If Len(TextBox1.Text & vbNullString) = 0 Then Exit Sub

    If KeyCode = 13 Then
        With Sheets("Dados")
            Columns("B:B").Select
            FindText = TextBox1.Text
            Ret = Selection.Find(What:=FindText, _
                                 After:=.Cells(1, 1), _
                                 LookIn:=xlValues, _
                                 LookAt:=xlWhole, _
                                 SearchOrder:=xlByColumns, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False, _
                                 SearchFormat:=False).Activate
            MsgBox "Hy " & FindText & " > " & Ret
        End With
    End If 

End Sub

You are missing a period in front of `Columns`... Also, the `Find` could be pulled out of the `With` [if you insist on using select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Chrismas007, Jan 13 '15 at 15:06
I understand your down vote @Chrismas007, but the code behaves exactly the same using .Columns and just Columns. It is good to explicitly denote them, but that is not the cause of the cause of the error ! — PaulFrancis, Jan 13 '15 at 16:01
My downvote was more for `Select` as I am pretty passionately against it (although I had some old code I just posted for another answer on PPT with `Select`... ugh) Also always better to define the period just to be clear but I got your point. — Chrismas007, Jan 13 '15 at 16:03
@PaulFrancis `Columns("B:B")` refers to column B of the Active sheet only, where as `.Columns("B:B")` refers explicitly to column B of the sheets "Dados". The code will only behave the same if "Dados" happens to be the active sheet at the time of execution. — SierraOscar, Jan 14 '15 at 17:53

score -1 · Answer 2 · edited May 23 '17 at 10:25

First off stop using .select. Secondly, you are missing a period before Columns to refer to your With...

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Ret As Boolean
Dim FindText As String
If KeyCode = 13 Then
    If TextBox1.Text = "" Then Exit Sub
    FindText = TextBox1.Text
    With Sheets("Dados")
        If .Columns("B:B").Find(What:=FindText, After:=.Cells(1, 1), LookIn:=xlValues, LookAt_
            :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase_
            :=False, SearchFormat:=False) is Nothing Then
            Ret = False
        Else
            Ret = True
        End If
    End With
    MsgBox "Hy " & FindText & " > " & Ret
End If
End Sub

Runtime error 91 - When using Range.Find and String from TextBox

2 Answers2