Using Loops for Repetitive Task

**fa888** · 10-10-2010, 09:55 PM

Hi I'm very new to VBA and am learning. I recorded a macro as below. Can it be simplified using a Loop function somehow? I would like to loop until Column A is empty. Thank you. Felix

Please Login or Register  to view this content.

**royUK** · 10-11-2010, 01:22 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**fa888** · 10-11-2010, 08:43 PM

Thank you RoyUK for pointing out my violations. I hope I meet the forum rules this time. Felix

**MarvinP** · 10-11-2010, 09:12 PM

This will give you a start

Please Login or Register  to view this content.

You can plug what you've selected directly into Sheets("analysis") without having to selecting it. Kind of like how I changed the first two lines of your code.

Also it will run faster if you set ScreenUpdating to false.

If you need to find the last row in A look at
http://www.mrexcel.com/td0058.html

You would use code like this:

Please Login or Register  to view this content.

hth

**fa888** · 10-12-2010, 09:54 PM

Thanks MarvinP, I tried your code and put in 1 line that was missing in my recorded macro. That's to copy value of cell BF22.

Please Login or Register  to view this content.

This code did not return to required result and I believe it is because I did not explain the situation clearly. I have attached a jpeg of the worksheets to describe what was intended.

The purpose is to fill up column C in PAIRS worksheet from Analysis!BF22.

(As a background, I have 45 people in my group and through the years they have paired up as a team. I am trying to get a summary of who paired with whom and how many times it happened).

Thanks. Felix

**fa888** · 10-20-2010, 02:13 AM

MarvinP, I worked on your Rowctr hint and got it to work exactly the way I wanted. That's after recording many many macros and seeing how the macros work and looking at your hint. Thank you v much.

Regrettably, I don't know what Rowctr stands for. Is it Row - center?

In any case, the end result that worked is

Please Login or Register  to view this content.

**MarvinP** · 10-20-2010, 10:40 AM

RowCtr is a variable that I create to tell me it is a "Row Counter".
I use it in loops to run down the rows.

ColCtr is my variable name to count columns or Column Counter.
Other people have their own styles of variable naming.

When I read code (including my own code) it is hard to remember what all those variables mean. I try to give them a name that tells what they are or what they do. Another favorite variable is LastRow.

If you start naming your variables in a way that makes sense you will be able to read you code much easier.