Using offset with in a loop

**hoffey** · 06-30-2009, 02:26 PM

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..

-------

**hoffey** · 06-30-2009, 03:27 PM

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??

**DonkeyOte** · 06-30-2009, 03:28 PM

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.

**hoffey** · 06-30-2009, 03:33 PM

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.

**hoffey** · 06-30-2009, 03:37 PM

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

Please Login or Register  to view this content.

Thanks DonkeyOte!

**hoffey** · 06-30-2009, 03:48 PM

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

**hoffey** · 06-30-2009, 05:49 PM

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.

Using offset with in a loop

LinkBack

Thread Tools

Rate This Thread

Display

Using offset with in a loop

Re: Using offset with in a loop

Re: Using offset with in a loop

Re: Using offset with in a loop

Re: Using offset with in a loop

Re: Using offset with in a loop

Re: Using offset with in a loop

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions