current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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!

share|improve this question

closed as off-topic by Jean-François Corbett, It's been a pleasure, Unheilig, Laf, djikay Aug 4 '14 at 18:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example." – Jean-François Corbett, It's been a pleasure, Laf, djikay
If this question can be reworded to fit the rules in the help center, please edit the question.
    
I'm sorry, forgot to mention it. It's the line beginning with MsgBox. –  stamblerre Aug 4 '14 at 14:46
3  
See THIS on how to use .Find –  Siddharth Rout Aug 4 '14 at 14:53
    
Also please avoid the use of .Select Also you may want to find the last cell in Col A using THIS and then use a FOR LOOP? –  Siddharth Rout Aug 4 '14 at 14:55

2 Answers 2

up vote 2 down vote

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
share|improve this answer
up vote 1 down vote

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.

share|improve this answer
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 4 '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 4 '14 at 16:03
    
@DavidZemens, thanks, good point. –  Lance Roberts Aug 4 '14 at 16:03
    
@stamblerre, shouldn't matter since you specify the exact sheets that you are using. –  Lance Roberts Aug 4 '14 at 16:04
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 4 '14 at 16:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.