CODEWITHSUNDEEP
excelvba
AXL
AXL

Reputation: 31

How to Use the Find Function

I have recently Written some code to take an input from a userform text box and search for it in my database. If found I would like it to return the value and insert it into cell A1149; I have written the below code but it gives me error #424 "Object Required". I am very new to VBA so any and all help is greatly appreciated.

Private Sub CMDSearch_Click()

     Dim pesquisa As Range

     Set pesquisa = Worksheets("Petrobras").Activate.Range("$W:$W") _
         .Find(What:=Opp_Num_Search.Value, LookIn:=xlValues, Lookat:=xlWhole).Activate

     Worksheets("Petrobras").Range(A1149).Value = pesquisa.Value

     UserForm1.Hide

End Sub

Upvotes: 1

Views: 70

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Range.Activate doesn't return anything, it's like a Sub procedure:

Public Sub DoSomething()
    ' does something...
End Sub

If you did Set foo = DoSomething.Something, you'd get the same error: an object is required, otherwise that .Something member call is illegal (except now the error would be at compile-time and not run-time, because of how binding works).

Set pesquisa = Worksheets("Petrobras").Activate...

You don't want to Activate any sheets.

Part of the problem is the implicit late-binding going on: Worksheets returns an Object, so everything you wrote after that, all these chained member calls, can only be resolved at run-time.

Make it early-bound, by declaring an explicit Worksheet variable:

Dim sheet As Worksheet
Set sheet = Worksheets("Petrobras")

Now if you want to activate it, you can do sheet.Activate (but you don't need to). And if you want to get a Range from that worksheet, you can make a .Range member call, and the IDE will now help you do it:

Dim result As Range
Set result = sheet.Range("$W:$W").Find(...)

NEVER chain any member calls to what Range.Find returns. If the search turned up a result, you have a Range object. If it didn't, you have Nothing - and any member call made against Nothing will always raise run-time error 91.

Validate the search result first:

If result Is Nothing Then
    MsgBox "Could not find '" & Opp_Num_Search.Value & "' in column W."
    Exit Sub
End If

Or:

If Not result Is Nothing Then
    sheet.Range("A1149").Value = result.Value
End If

Note that A1149 is a string literal representing a cell address, and as such it must be surrounded with double quotes ("). If it's not in double quotes and it looks like a valid variable name, VBA will treat it as ..a variable... and that will cause yet another error (1004), because Range will be rather unhappy to work with a Variant/Empty value.

To prevent VBA from "declaring" on-the-fly variables with a typo (and causing hard-to-find bugs), make sure you have Option Explicit at the very top of every module in your project.

One last thing:

 UserForm1.Hide

This hides the default instance of UserForm1, which may or may not be the current object / instance that's shown - and the form itself has no way to know how it was shown:

UserForm1.Show '<~ shows the default instance

With New UserForm1
    .Show '<~ shows a new (non-default) instance
End With

For this reason, you should avoid referring to the default instance in a form's code-behind. Use Me instead:

Me.Hide

That way you're referring to whatever instance of the form is currently shown, whether that's the default instance or not. See UserForm1.Show for more information, tips, pitfalls and common mistakes involving userforms.

Note that Rubberduck has quite a few inspections that can identify and warn you about (and often, automatically fix) most of these problems. Rubberduck is a free and open-source VBIDE add-in project I manage.

Upvotes: 2

Related Questions