+ Reply to Thread
Results 1 to 4 of 4

Find Creates Error on Second Iteration

  1. #1
    Registered User
    Join Date
    09-22-2010
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2003 and Excel 2010
    Posts
    3

    Find Creates Error on Second Iteration

    Hello,

    I am attempting to find a variable string in a range and then putting a formula in the cell 6 columns over. The entire process is in a loop so that the variable will change and a different cell will be found each time. The first iteration works perfectly, the second always bugs out with a Run time error 91: Object variable or With block variable not set.

    I tried every combination of things I could think of, I hope someone here can help.

    I am taking data from one workbook to another, so the data goes into destWS in destWB (destination). Data is copied to lines sequentially in destWS, so I have a destRow that tracks which line gets filled next and then adds 1 each iteration.

    The Find code originally omitted activating cells and all that, but I changed it in hopes of it working and this is what's left. I cut out a lot of code above and below because it hopefully isn't related.

    Please Login or Register  to view this content.
    I'm hoping it's a simple quirk about find or objects that I don't know.

    Thanks for your help!

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Find Creates Error on Second Iteration

    You don't need to select or activate.

    Without seeing more of the code it's impossible to comment further.

    Why not attach a dummy workbook
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    09-22-2010
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2003 and Excel 2010
    Posts
    3

    Re: Find Creates Error on Second Iteration

    I attached a dummy file with three worksheets. The first is lookup info. The second is destWS, and the third is the information that is in another book. The VBA is in the workbook as well (it ain't pretty).

    Everything worked until I added the find lines. The data reads in just fine from the other worksheet, and the rest works well.

    I really think I am messing up something with the objects or ranges. They work fine the first iteration, and bug out the second.

    Thanks for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-22-2010
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2003 and Excel 2010
    Posts
    3

    Re: Find Creates Error on Second Iteration

    Oh I found it. Thanks for making me look through it all!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1