Help with a VBA macro ending with a LOOP

**5h1l** · 07-11-2012, 10:54 AM

Hi Guys

I have a macro created and it starts on the cell which the user specifies and ends on the cell below. Is there a way in which i can have it looped until the end cell is empty?

Many Thanks

Shil

--------------------------------

Please Login or Register  to view this content.

**fredlo2010** · 07-11-2012, 01:04 PM

Can you include a small sample of the workbook and a description of what the macro does?

**5h1l** · 07-12-2012, 11:39 AM

Please find attached the example.

WorkSheets:

Payroll calculations - is the are which calculates the pay for that employee.
Bank File - Takes the information from the Payroll Calculations and deploys it in the specific method we need.

The current process is that you press Cntrl + n to prepare the first two rows in bank file.
Then Clicking on cell A5, press cntrl + m to copy the record from payroll calculations to the bank file. It ends the macro in the cell below you started. You have to keep on pressing cnrtl + m until the macro ends in cell A18 (First empty Cell).

What i would like to do is to keep the macro on a loop so that it loops until it finds the empty cell.

I hope this helps.

Please let me know should you require further information.

Thanks

Shil

**JapanDave** · 07-12-2012, 11:50 AM

Select a cell and click the button.

**5h1l** · 07-12-2012, 11:54 AM

Hi Japan Dave.

Your button only takes one line and transfers it. It doesn't loop all the lines?

Thanks

Shil

**JapanDave** · 07-12-2012, 12:55 PM

See if this is what you are after?

**5h1l** · 07-13-2012, 06:10 AM

No im afraid not Japan Dave. Your file only copies the one row and copies accross the same row.

I would like the macro to move accross one row, one at a time until it comes to the empty cell.

Shil

**5h1l** · 07-13-2012, 06:22 AM

So here is my current macro code:

Please Login or Register  to view this content.

After looking online i think i found the code for the loop:

Please Login or Register  to view this content.

The only part where im struggling is how to edit the current code so it includes this second code i found online.

I hope someone knows the answer.

Thanks

Shil

**JapanDave** · 07-13-2012, 06:25 AM

No it does not. It copies down from the row you select. If you select range("AV5") it will copy all rows from there to the last row in "AV5" into sheet "Bank File".

**5h1l** · 07-13-2012, 06:33 AM

When i click on Cell AV5 and click on your button it takes that row and duplicates this all the way down. Look at column 24 for confirmation (in the bank file worksheet). It comes up with Bernadita all the way down. Whereas i'm expecting to see All the other names here.

Perhaps im running it incorrectly?

**JapanDave** · 07-13-2012, 06:47 AM

Or perhaps you are not explaining yourself correctly. Or I am not understanding you correctly?
My code copies all the values from Range("AV5:CZ5") to Range("AV22:CZ22") into "Bank File". If you want different data copied you are going to need to specify it.

**5h1l** · 07-13-2012, 10:03 AM

Ok so should i be in cell AV5 and then click on the button or should i be selecting the whole range and then clicking on the button?

I have tried both and they give the same outcome. They both replicate the first row (Row 5) downwards and it doesnt take the rows beneath row 5 and copy these accross.

**JapanDave** · 07-13-2012, 01:25 PM

You should only need be in AV5.
And of coarse it will replicate that row, b/c your file has the same data in the following rows until the empty one. Can you not see what is happening? It copies row 5 from column AV, then row 6 , row 7. Your data is exactly the same in these rows. If you don't what the same data, you need to specufy where you what it copied. Or you need to change the data.

**5h1l** · 07-16-2012, 04:48 AM

The file does not have the same data as in all the rows until the empty one. E.g Column BS, appears to have a list of Names. In fact its pointing to a list of names. But all the names are different. After running your button, the names on the bank file appear to be the same.

Regards

Shil

**romperstomper** · 07-16-2012, 06:16 AM

Try this:

Please Login or Register  to view this content.

**JapanDave** · 07-16-2012, 08:34 PM

Thanks Romperstomper, I completely missed that.

@5h1l , sorry about that, my code was not doing what I said it would.

It should have been this on this line.

Please Login or Register  to view this content.

**5h1l** · 07-24-2012, 12:20 PM

Originally Posted by romperstomper

Try this:

Please Login or Register  to view this content.

Thanks romperstomper. The code worked well, however i need the macro to do copy and paste row 2 (Which is part of a header record, and it gets there by typing in Ctrl+N) inbetween each line that has been copied accross.

Any idea how to do this?

Thanks

Shil

**5h1l** · 07-27-2012, 11:57 AM

Hi Guys, im wondering if i havent explained myself enough:

Here is the code:

Please Login or Register  to view this content.

This code is copying and pasting a particular row (Code in Red) and pastes to a different worksheet.

Then it returns in the cell below the last copied cell.

What i need to do is to somehow write a loop like the below which will keep on copying and pasting the rows to the other worksheet until it comes to an empty cell:

Please Login or Register  to view this content.

Does anyone know how to combine the two codes, or to edit my code so that it keeps on looping until it finds an empty cell?

Help will be greatly appreciated.

Thanks

Shil

**romperstomper** · 07-27-2012, 12:08 PM

Without knowing the activecell at the start, it's impossible to rewrite that code. Based on your comment about my last code, I think perhaps:

Please Login or Register  to view this content.

If not, please post before and after samples so we can see what the output should be exactly.

**5h1l** · 07-30-2012, 10:42 AM

Hi Romper Stomper, the active cell always starts in cell A5, then when it returns its in A6. Then i would execute the macro again so that it returns to A7. etc etc. Until it gets to the first blank cell, at which point i would move on to the next step.

Does this help?

Thanks

Shil

**romperstomper** · 07-30-2012, 11:53 AM

Please comment on the last code.

**5h1l** · 07-31-2012, 09:58 AM

Thanks, this solved it!

Regards

Shil

Help with a VBA macro ending with a LOOP

LinkBack

Thread Tools

Rate This Thread

Display

Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Re: Help with a VBA macro ending with a LOOP

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions