Basic For/Each question

**zdodson** · 04-10-2013, 01:34 AM

Hello community,

I am just beginning to learn how to implement loops, so bare with me here in the thread.

I have a dynamic range on Sheet 1 that uses the following offset formula to return a range from A2--that's where the records actually start--and the last used row in the column. For the purposes of this thread, I'll call it "MyRange".

In Sheet 2, I have a form that is intended to loop through "MyRange", and plug each cell in "MyRange", one-by-one, into Sheet 2, Cell A1.

After it drops that name, the form will gather the rest of that respective entry's data from Sheet 1.

Here is the code I was working with:

Please Login or Register  to view this content.

Any suggestions?

Zack

**Norie** · 04-10-2013, 02:56 AM

A few things not quite right.

The first thing is that you need to find the last row before you set the range Alpha.

Also, with the code as it is that range will only be one cell.

Finally, no need to use select.

Please Login or Register  to view this content.

**zdodson** · 04-10-2013, 12:19 PM

Thanks for the suggestion, Norie!

Unfortunately, it is returning a Run-time error 1004: Application-defined or object-defined error.

After I used the F8 debugger to see what was going on, it says the following:

ws.Range("$A$2:A" & LastRow) = <Method 'Range of object '_Worksheet' failed>

Any suggestions?

Zack

**Norie** · 04-10-2013, 01:01 PM

My bad, finding last row should come after setting the worksheet reference.

Please Login or Register  to view this content.

PS Do the sheet names definitely have a space after 'Sheet'? Might be worth checking.

**zdodson** · 04-10-2013, 05:15 PM

Thanks Norie! Works beautifully!

Is there a way to return another portion of that record in the next cell in Sheet2?

Let's say that Sheet 2, $A$1 is your name, and that Sheet 2, $A$2 is the location for that record.

Is there a way to do that in for each loop?