3 Replies - 2888 Views - Last Post: 12 June 2013 - 04:41 AM Rate Topic: -----

#1 Monte31  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 07-February 13

VBA .findnext not working. Runtime error 91 object variable not set

Posted 10 June 2013 - 07:01 AM

I am trying to execute a search where it searches through a column for "REQM" (no quotes) and set the range of the found cell to d. Then call another sub function that finds where to enter the data into. My FindEntryArea sub function works fine and my first find works great but when it tries to findnext it is not working properly.

Sub FindLoop()
With re
    Set d = .Find("REQM", LookIn:=xlFormulas, LookAt:=xlWhole)
    MsgBox (d.Row)
    Call FindEntryArea
    Do
        Set d = .FindNext(d)
        MsgBox (d.Row)
        Call FindEntryArea
    Loop While Not c Is Nothing
End With


End Sub


Trying to figure out the error I used msgbox to print out the row of the range that was being found this worked fine for the first cell but did not work for the findnext. I get object variable or with block variable not set. I am fairly new to VBA and this is my first time using findnext so any guidance would be appreciated. Also re is my range and there are plenty of other cells that should be found within it.

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: VBA .findnext not working. Runtime error 91 object variable not set

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,780
  • Joined: 26-March 09

Re: VBA .findnext not working. Runtime error 91 object variable not set

Posted 10 June 2013 - 01:59 PM

Check out this link for some pointers about using find and findnext.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3526
  • View blog
  • Posts: 12,037
  • Joined: 12-December 12

Re: VBA .findnext not working. Runtime error 91 object variable not set

Posted 10 June 2013 - 02:28 PM

You haven't told us which line you are taken to when the error occurs.

But there is no c, it should be d:

Loop While Not c Is Nothing

I strongly recommend that you add Option Explicit at the top of your module, which will force you to declare all variables. This will, among other things, pick up the error with c straight-away.

Additionally to maj3091's link you can simply click into the word Find or FindNext and press F1 to access the help system, where is found:

Quote

When the search reaches the end of the specified search range, it wraps around to the beginning of the range. To stop a search when this wraparound occurs, save the address of the first found cell, and then test each successive found-cell address against this saved address.

Was This Post Helpful? 1
  • +
  • -

#4 Monte31  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 07-February 13

Re: VBA .findnext not working. Runtime error 91 object variable not set

Posted 12 June 2013 - 04:41 AM

Thank you for the responses but I found my problem. c was a typo and should have been d but the real problem was when I was calling find entry area there was another find in that procedure so I couldn't use findnext. To fix this instead of using findnext I just used find and set the after argument to be after d. Works great now.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1