+ Reply to Thread
Results 1 to 7 of 7

Using offset with in a loop

  1. #1
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Using offset with in a loop

    Please Login or Register  to view this content.
    --

    OK, I may be over my head here, but I am trying to (1) find occurrences of "Project" in column A, and (2) set a variable to the address of the first occurrence, then (3) use the variable to perform a series of Offset commands to grab data [which will have to be pasted to a separate worksheet, but I can figure that out]

    I am having difficulties figuring out how to set the variable equal to the active cell. How would I do an offset on strProj ?

    Any help is appreciated..

    -------
    Last edited by hoffey; 06-30-2009 at 03:24 PM.

  2. #2
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Using offset with in a loop

    Sorry about that...


    Please Login or Register  to view this content.
    Can't seem to figure out how to reset the spid variable to nothing so the next time it loops the MsgBox will display the updated offset. any ideas??

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Using offset with in a loop

    EDIT: the below applies to post 1.. code in post 2 updated negating this point:

    I suspect you want to be using Cell as opposed to ActiveCell as the anchor in your Offset command.


    Re: resetting spid - either

    a) prior to Next
    b) prior to If

    Please Login or Register  to view this content.
    As I se it spid is obviously highlighting number in A that is 2 rows below instance of project... note boundaries of Integer are whole numbers between -32768 to 32767 if your values sit outside of those parameters you may well get an Overflow error.
    Last edited by DonkeyOte; 06-30-2009 at 03:32 PM.

  4. #4
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Using offset with in a loop

    Please Login or Register  to view this content.
    This has the desired effect, the MsgBox displays correctly when it is set to the Offset of the range. What I want to be able to do is set the offset to a variable, which will be pasted into a separate worksheet. This needs to occur before the next loop however.

  5. #5
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Using offset with in a loop

    Weird, I set spid to a string at it works..

    Please Login or Register  to view this content.
    Thanks DonkeyOte!

  6. #6
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Using offset with in a loop

    Please Login or Register  to view this content.
    Now I'm having trouble copying spid into the newly created worksheet. Do I have to declare it as a range in order to do so? I know I'm missing something really easy here..

    *Note I deleted what I had to copy because it didn't work

  7. #7
    Registered User
    Join Date
    06-30-2009
    Location
    Southern California, Earth
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Using offset with in a loop

    Got a little bit farther, now instead of Debug.Print I just need to transfer the data onto a worksheet into certain ranges. Any ideas on how to copy this data so I can paste it?

    Again, any help would be appreciated, thanks in advance...

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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