Need a simple macro to loop - don't know how?

[rhess]

I'm trying to get this macro to loop and have no idea what I need to do do to get the macro to keep repeating until it gets to the last item in a list.

I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.

Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.

I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.

Sub Transpose_j()
'
' Transpose_j Macro
' Transpose
'
' Keyboard Shortcut: Ctrl+j
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


My list looks like this before I run the macro ... only longer:

American Community Bank
300 Glen Street
Glen Cove, NY 11542 3070

American Stock Transfer & Trust Company
59 Maiden Lane
New York, NY 10038 4667
Mail To : 59 Maiden Lane, Plaza Level
New York, NY 10038 4667

American Stock Transfer & Trust Company,
LLC
59 Maiden Lane
New York, NY 10038 4667

Anthos Trust Company, LLC
277 Park Avenue
New York, NY 10172

Banco Popular North America
7 West 51st Street
New York, NY 10019
Mail To : 120 Broadway
New York, NY 10271

My goal is to use paste special to transpose each one & then I'll have something that looks like this:

<TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Community Bank</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>300 Glen Street</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>Glen Cove, NY 11542 3070</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 465pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=619 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10038 4667</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Mail To : 59 Maiden Lane, Plaza Level</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=555 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company,</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>Anthos Trust Company, LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>277 Park Avenue</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10172</TD></TR></TBODY></TABLE>


I don't care if it delete's the oringial rows or not ... I can always sort to get rid of those.

Thanks in advance!

 

[Norie]

It seems that the format has perhaps been mucked up when posting.

Is this what you want to end up with?

<TABLE style="WIDTH: 710pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=945 x:str><COLGROUP><COL style="WIDTH: 200pt; mso-width-source: userset; mso-width-alt: 9728" width=266><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><COL style="WIDTH: 174pt; mso-width-source: userset; mso-width-alt: 8484" width=232><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 200pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=266>American Community Bank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=118>300 Glen Street</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 125pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=166>Glen Cove, NY 11542 3070</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 174pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=232> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 122pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=163> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>American Stock Transfer & Trust Company</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>59 Maiden Lane</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10038 4667</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mail To : 59 Maiden Lane, Plaza Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10038 4667</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>American Stock Transfer & Trust Company,</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>LLC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>59 Maiden Lane</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10038 4667</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Anthos Trust Company, LLC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>277 Park Avenue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10172</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Banco Popular North America</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>7 West 51st Street</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10019</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Mail To : 120 Broadway</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>New York, NY 10271</TD></TR></TBODY></TABLE>

 

[Blade Hunter]

Why wouldn't you just use a mail merge to labels in Word?

 

[Norie]

If you do want to transpose the data as I've suggested then you could use this simple macro, assuming the original data is in column A.

Option Explicit
Sub TransAddr()
Dim rngSrc As Range
Dim rngDst As Range
Dim arAddress As Range
    Set rngSrc = Columns(1).SpecialCells(xlConstants)
    Set rngDst = Range("B1")
    
    For Each arAddress In rngSrc.Areas
            arAddress.Copy
            rngDst.PasteSpecial xlPasteAll, Transpose:=True
            Set rngDst = rngDst.Offset(1)
    Next arAddress
End Sub

The result would appear in column B onwards.

 

[rhess]

Yes, Norie, your table is what I want the data to look like.
Thanks,
RHess

 

[rhess]

Thanks! That's exactly what I wanted to do ... now, if I only understood what made that work?!

How did you do that without using any sort of loop? I guess you did use a loop in there.

 

[Blade Hunter]

Thanks! That's exactly what I wanted to do ... now, if I only understood what made that work?!

How did you do that without using any sort of loop? I guess you did use a loop in there.

This is the loop:

    For Each arAddress In rngSrc.Areas
 
    Next arAddress

 

[Norie]

There is a loop in there, but it's one type of loop you didn't really mention in your first post - a For Each loop.

Now I'm probably not going to explain it very well, or in fact correctly.

But you can use a For Each loop to go through a collection of objects.

In this case those objects are Areas.

The first part of the code creates a range that includes all the Areas in column A.

Then they are looped through and the copy and paste/transpose is done for each one, moving down a row in column B for each.

That's probably as clear as mud.

But it seemed to work with the data you posted.

There are definitely other methods to do this sort of thing but this was the first one that sprang to mind to me.

 

[Blade Hunter]

There are definitely other methods to do this sort of thing but this was the first one that sprang to mind to me.

I like it, I would have done For Next simply because that is what I use most but I think the For Each is a nicer method in this instance I really must keep this in mind more often .

 

[Norie]

BladeHunter

The For Each is really just a For Next loop, just dealing with collections/whatever rather than hardcode values like in say something like this.

For I=1 To 10
 ' code here
Next I

Mind you it isn't always needed to hard code the values there.

For example with an array.

arrValues = Array(1,2,3,10)
 
For I = LBound(arrValues) To UBound(arrValues)
      Msgbox arrValues(i)
Next I

But then again you could still use For...Each here.

arrValues = Array(1, 2, 3, 10)
 
For Each x In arrValues
      MsgBox x
Next x

Horses for courses I think.