Excel VBA Application-defined or object defined error

Question

I'm struggling with this bit of VBA code. For some reason I keep getting: "Run-time error '1004': Application-defined or object defined error" message. My only thought on why this may be is that I have the macro associated with a command button that is tied to a specific sheet - if this is the error, how can I resolve it? Otherwise, what might be the error here?

The line causing the error is the one beginning with MsgBox.

Sub Process()
    Sheets("Intermediate").Cells(2, 1).Select
    Dim contains As Range, lastRow As Long
    Do Until IsEmpty(ActiveCell)
        MsgBox Sheets("Document Library").Columns(1).Find(ActiveCell.Value)
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Thank you!

I'm sorry, forgot to mention it. It's the line beginning with MsgBox. — stamblerre, Aug 04 '14 at 14:46
See [THIS](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) on how to use `.Find` — Siddharth Rout, Aug 04 '14 at 14:53
Also please avoid the use of [.Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) Also you may want to find the last cell in Col A using [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) and then use a FOR LOOP? — Siddharth Rout, Aug 04 '14 at 14:55

score 2 · Answer 1 · answered Aug 04 '14 at 16:38

In addition to using Sid's link as a reference, it's worth noting that the various setting for Find() are persistent - that is, if you used Find in a particular way (either via the UI or using VBA) then the next time you call Find those same settings will be used unless you specify otherwise.

So, it's always good practice to specify all the settings you care about (such as lookAt, lookIn) every time you use Find or you may not get the results you expect.

Also, you need to handle the event where you don't find the value being searched for - in this case Find returns Nothing:

Sub Process()
    Dim f as Range, c As Range, rngSearch as Range

    Set rngSearch = Sheets("Document Library").Columns(1)
    Set c = Sheets("Intermediate").Cells(2, 1)

    Do While Len(c.Value)>0
        'specify exactly how you want Find() to operate....
        Set f = rngSearch.Find(What:=c.Value, lookin:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            debug.print c.value & " found at " & f.address()
        Else
            debug.print c.value & " not found!" 
        End If
        Set c = c.Offset(1, 0)
    Loop

End Sub

score 1 · Answer 2 · answered Aug 04 '14 at 15:59

1

The Range.Find method returns a Range Object. The MsgBox function requires a string expression to output. So try this:

MsgBox Sheets("Document Library").Columns(1).Find(ActiveCell.Value).Value

Note that depending on the type of the value in the cell, you may need to do a conversion to String also.

answered Aug 04 '14 at 15:59

Lance Roberts

22,383
32
112
130

1

+1. For OP, do note that this line will continue to error *if* the result of `.Find` is `Nothing` i.e., the value is not found. Additional logic is required to test for that and avoid the error :) – David Zemens Aug 04 '14 at 16:02
hm, still doesn't work for some reason, but thank you. I'm thinking that my issue has to do with having multiple worksheets going, as the sheet that I'm finding in isn't the active sheet. Could that be the case? – stamblerre Aug 04 '14 at 16:03
@DavidZemens, thanks, good point. – Lance Roberts Aug 04 '14 at 16:03
@stamblerre, shouldn't matter since you specify the exact sheets that you are using. – Lance Roberts Aug 04 '14 at 16:04
are you getting an error still? If so, what error? @stamblerre – David Zemens Aug 04 '14 at 16:06
@stamblerre, you should troubleshoot, by putting a breakpoint on that line, then using the Immediate window to check values using the Debug.Print command. – Lance Roberts Aug 04 '14 at 16:07
Yea, I'm trying to do that and I can't get it to show me anything meaningful. I've changed the MsgBox line to just this `contains = Sheets("Document Library").Columns(1).Find(ActiveCell.Value)` and now the error is "object variable or with block variable not set" – stamblerre Aug 04 '14 at 16:23
Well, did you change the 'DIM' statement for 'contains' so it isn't a range? (Variant will be fine for testing). – Lance Roberts Aug 04 '14 at 16:27
Sorry, not sure I understand (I am really new to VBA). I thought you said that Range.Find returns a Range object? – stamblerre Aug 04 '14 at 16:29
1

Default Range property is Value, so there's no need to explicitly add `.Value` (which is not to say that's not good practice) – Tim Williams Aug 04 '14 at 16:32
@stamblerre, yeh, too early for me. You need to use the 'Set' command to assign that value to 'contains' then. – Lance Roberts Aug 04 '14 at 16:35

Excel VBA Application-defined or object defined error

2 Answers2