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!

 

[Blade Hunter]

Yeah I just find that by default I will do something like:

For X = 1 to range("A" & rows.count).end(xlup).row
Range("B" & X).formula = "something"
next

And I think a for each is better in this instance

For each cell in range("B1:B" & range("A" & rows.count).end(xlup).row)
Cell.formula = "something"
next

Like you said, horses for courses but I think the for each looks a little cleaner, I just keep forgetting to use it

 

[Norie]

The only problem I think there might be with using For...Each is that sometimes you can't be always be sure the loop is going to work in the order you expect.

As for the code you posted, if it's as simple as that then there is probably no need for the loop.

Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row) = "Something"

Like I said if it's as simple as wanting to put a value into the adjacent column based on the no of rows with data in the other column.

 

[Blade Hunter]

The only problem I think there might be with using For...Each is that sometimes you can't be always be sure the loop is going to work in the order you expect.

As for the code you posted, if it's as simple as that then there is probably no need for the loop.

Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row) = "Something"

Like I said if it's as simple as wanting to put a value into the adjacent column based on the no of rows with data in the other column.

Hehehe it was just a freetyped example . Lets make it "Something" & Range("C" & Cell.row).value

 

[Norie]

Well I thought it was a simple example.

Might not be a need for a loop though - really depends what you are trying to do.

If it was as simple as concatenating then I would perhaps think about adapting what I posted to create a formula in column B.

Then, if required a copy/paste special.

With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=C1& ""Something"""
    .Copy
    .PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False