Report

Repeating a simple macro loop up to 1,500 times [Solved]

Ask a question shassall 2Posts Monday July 17, 2017Registration date July 18, 2017 Last seen - Last answered on Jul 20, 2017 at 11:29 AM by TrowaD
Hi All,

Office 2013 user here with a first time macro question...

I have a large XLSM file with simple text labels in Column A. There are 1,500 records and each record is grouped in 4 consecutive cells (A1,A2,A3,A4) with a space between each record.

I'm trying to create a simple database file where each of the data points is arranged on the same line (i.e. A1/B1/C1/D1) etc.

I recorded a simple test macro which goes through 4 loops to move the data the way I want. But I don't know how to keep the loop running for all the records in the total database.

I was excited that I figured out how to do a simple test loop, and it went through the 4 data records that I recorded, but not sure how to specify that it loops 1,500 times until it acts upon all the records in the current database.

I should mention that I don't know VBA.

Any help is truly greatly appreciated!

Thank you!
Steve
Helpful
+0
plus moins
Hi Steve,

The code below will transpose your grouped data to columns B:E. You can then manually delete column A, to shift all data to the left. Or you can remove the apostrophe in front of code line 11 to let the code remove it for you.

Here is the code:
Sub RunMe()
Dim x, y As Integer
x = 1
y = 1
Do
    Range(Cells(x, "A"), Cells(x + 3, "A")).Copy
    Range("B" & y).PasteSpecial Transpose:=True
    x = x + 5
    y = y + 1
Loop Until Cells(x, "A") = vbNullString
'Columns("A").Delete
Application.CutCopyMode = False
End Sub


Hopefully this will get you excited again!

Best regards,
Trowa
shassall 2Posts Monday July 17, 2017Registration date July 18, 2017 Last seen - Jul 18, 2017 at 07:01 PM
Hi Trowa,

WOW! I am sure I would not have known how to do this, so thank you very much for your quick response. I will try it tonight and report back....

I'll assume rhetorically, that I should create a macro and paste this code into it? If I get stuck, I'll ask another question.

Thanks!
Steve
Reply
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 Last seen - Jul 20, 2017 at 11:07 AM
Hi Steve,

In case you get stuck on that:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Reply
stevehassall- Jul 20, 2017 at 11:17 AM
Thanks Trowa!

I opened the existing macro that I had created to run four manual loops and overwrote that code with your macro. It worked perfectly!!!! (Of course you knew that! :-)

Thank you so much, I couldn't have figured this out without your help...and I'm somewhat of an Excel guru...just not a macro guru!

Steve
Reply
TrowaD 2282Posts Sunday September 12, 2010Registration date ModeratorStatus October 19, 2017 Last seen - Jul 20, 2017 at 11:29 AM
Awesome Steve!

And be careful, before you know it you are sucked into the world of macro's!!! ;)
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!