Problem Using the Find Method 1

[kjv1611]

Okay, so I was trying to make a particular method work more efficiently by using the Find method rather than a loop, and yes, when I use this totally by itself it works TONS better and faster.

Here is the portion of code of reference:

    Cells.Find(What:="Unique ID", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Activate

It comes immediately after another portion which chooses the last cell with any values in it in column A.

When I run this code in a module totally to its own, it has no problems and works just fine.

However, when I have it following the other code, as mentioned, it gives me this error:

Run-time error '91':

Object variable or With block variable not set

So, my problem is: why in the world does it only happen under certain conditions? It is the EXACT same code whether by itself or in another procedure.

I found the code first here:

http://www.ozgrid.com/VBA/find-method.htm

Then, I decided to troubleshoot by recording a macro and getting it directly from Excel - it came out the same, basically.

Surely, one of the experienced gurus around here has a clue on this one.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Perhaps this ?
[!]ActiveSheet.[/!]Cells.Find(What:="Unique ID", After:=[!]ActiveSheet.[/!]Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Numbers1]

I just wrote the following sub and then call it when I want to search for something.

Public Sub subFind(ByVal FindItem As String)

' Find an item loaded from outside procedure
Cells.Find(what:=FindItem, After:=ActiveCell, lookat:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate

End Sub

Hope this helps, Numbers

Thanks,
Numbers

 

[xlbo]

kjv1611 - please post the code immediately before and after your find code when it bombs out...probably going to be what PHV has suggested however...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

I'll give PHV's suggestion a try, and post back.

In the mean time, here's the whole procedure that I currently have. It is, of course, not finished. I also had some portions commented out, which I just removed, b/c that code was not running at all during the time I was having these problems, and is not directly related.

Public Sub EnterFormula()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim x As Long 'Count Rows
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    Dim rTitle As Range 'Row where Column titles are located.
    Dim br As Long 'Row - used to find very last row with actual records
    Dim tr As Long 'Row - used to find the very top row with actual records


    Worksheets(ws.Index).Copy After:=wb.Worksheets(ws.Index)
    x = 1
    Range("H:H").Insert Shift:=xlToRight
    Range("A1").Activate

    
    Range("A65536").End(xlUp).Select
    Range("A65536").End(xlUp).Activate

    br = ActiveCell.Row
        
    Cells.Find(What:="Unique ID", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Activate
    rTitle = ActiveCell.Address
    rTitle.Select
    tr = rTitle.Row
    
    
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, I tried PHF's suggestion, and got the same error message. It just seems really strange that it happens if the code is within this one procedure, but not if I put it within its own procedure.

If worse comes to worse, I'll just do like what Numbers1 suggested. This is actually what I was considering doing yesterday, but I wanted to see if anyone else knew WHY this is acting this way, and if there were a way to correct the issue.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And it may be something else in the code causing the issue, I'm not sure. If I just call the Find procedure from a Public procedure, that part runs fine, but the next part gives the same error!

Here is the code for doing it this way:

[green]'First, the public procedure doing the Find action[/green]
Sub FindWithoutSelection()

Cells.Find(What:="Login ID", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
End Sub



[green]'And the code in question, highlighted where stopping with error[/green]
Public Sub EnterFormula()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim x As Long 'Count Rows
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    Dim rTitle As Range 'Row where Column titles are located.
    Dim br As Long 'Row - used to find very last row with actual records
    Dim tr As Long 'Row - used to find the very top row with actual records


    Worksheets(ws.Index).Copy After:=wb.Worksheets(ws.Index)
    x = 1

    Range("H:H").Insert Shift:=xlToRight
    Range("A1").Activate
    
    Range("A65536").End(xlUp).Select
    Range("A65536").End(xlUp).Activate

    br = ActiveCell.Row

    Call FindWithoutSelection
    
    [highlight]rTitle = ActiveCell[/highlight]
    rTitle.Select
    tr = rTitle.Row
    
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And what's really strange to me is that if I comment each line that has the error, I get the same error on yet the next line!

Is it possible that I'm just totally missing something? Yeah, it's possible. But it just seems strange. It's probably some small little bitty something I left out or commented out when I shouldn't have, I guess.. Let me know if anyone has a clue on what it could be.

Thanks!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

and what about this ?

Public Sub EnterFormula()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim br As Long 'Row - used to find very last row with actual records
    Dim tr As Long 'Row where Column titles are located.

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    ws.Copy After:=ws
    Set ws1 = wb.ActiveSheet

    ws1.Range("H:H").Insert Shift:=xlToRight
    br = ws1.Range("A65536").End(xlUp).Row
    tr = ws1.Cells.Find(What:="Unique ID", After:=ws1.Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).Row
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

PHV,

You are DA MAN!

Thanks a ton!

I guess for one thing, I had let the whole thing get kind of hairy, in that I had gone and changed this, added that, taken away this, and well, it was a mess. That alone will make it much quicker, shorter, easier to read, and more efficient!

Something makes me think you've messed with this stuff for a little while!



--

"If to err is human, then I must be some kind of human!" -Me