Loops

[Chuck W]

Hi, I am trying to learn about using loops to repeat a macro. For
example I have a list of items in Column C, one item on every line. I
want to copy them to say Column E with an empty cell below every item.
I used the "Record Macro to get the code to do it once.
I think the problem is that when it repeats it goes back to C2 rather
than move down the list. When reading about loops I think I understand
it but evidently I don't . I've used Excel but only the basics.


I would like to learn how to do three things.
1. Have the Macro repeat down the column until it comes to an empty
row?
2. And for future use have it do it for a set number of times ex. 20?
3. Where do I put the new code in the nacro I recorded ?




Sub learnloops()
'
' learnloops Macro
' Macro recorded 9/5/2003 by
'
' Keyboard Shortcut: Ctrl+c

Range("C2").Select
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C3").Select
Selection.Copy
Range("E4").Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub

Thanks,

Chuck

 

[Don Guillett]

x=cells(65536,"a").end(xlup).row will give you the last row in col A

for 1 to 20 'will give you the times the loop runs
code
loop

 

[Don Guillett]

Corrected to:
x=range("c65536").end(xlup).row
y=range("e65536").end(xlup).row+1
for each c in range("c1:c" & x)
c.copy range("e" & y)
next

 

[Chuck W]

Don, Sorry for that I'm new to the group. I assume ng is news group.
What is coding attempt. Also could you tell how to search for a
particular message. I tried putting in the date and time but that
didn't work.So at present I'm going back one by one or searching by
topic. Will only post within the group in the future.

Thanks for the help,

Chuck

 

[Don Guillett]

ng means newsgroup
I searched for you by loops or name
coding attempt. What macro code did YOU try?

BTW. Did what I sent help??

 

[Chuck W]

Don, I did not get a chance to try it until a few minutes ago.(Had to
go out for some Grandfather babysitting duties). The line " for each c
in range("c1:c"x)" came up Compile error Syntax error. There a place
for me to click for Help but when I did I got a blank Visual Basic
screen. Any ideas?

Chuck

 

[Myrna Larson]

If the line reads as shown in your message, it should be

For Each c in Range("C1:C" & X)

AIR, VBA Help doesn't get installed by default, so you'll have to go to Add/Remove programs and
do a Change on Office: be sure you find the VBA Help (I don't remember where it is off hand) and
check it.

 

[Chuck W]

Don, Were making progress, its copy every item from column C but it is
putting everything in E2 when the macro is done E2 shows the last
item in the column instead of putting the second item in E4, 3rd item
in E6. Also could you explain a couple of things to me? Am I right
that the xlup takes the cursor from cell in row 65536 up to the first
cell that has something in it just as if I hit End and the up arrow on
the computer? If so what does the .row and the .row +1 do. I would
have thought that after the first loop and there item from c1 was
copied to E2 that the end up would have gone to E2.

Thanks again

Chuck

 

[Chuck W]

Don, I did get your message and I changed the (for each c in
range("c1:c"x) to for each c in range("c1:c" & x). This solved the
copying problem but I'm stuck on the paste part. It is copying every
item from column C but it is
putting everything in E2 when the macro is done E2 shows the last
item in the C column instead of putting the second item in E4, 3rd
item
in E6. I step though the macro and I could see where it was copying
everything correctly but when it did the second paste it went to E2
again and overrode the first paste.

Chuck

 

[Don Guillett]

You are correct when you say "xlup takes the cursor from cell in row 65536
up to the first"
So row+1 will add a row.

POST your complete code.

 

[davers]

It looks like you almost had it...in the following example:


Code:
--------------------

Sub lrnLoop()

x = Range("C65536").End(xlUp).Row
y = Range("E65536").End(xlUp).Row + 1

For Each i In Range("C1:C" & x)
i.Copy Range("E" & y)
Next i

End Sub
--------------------


It couldn't loop the pasting because y always comes up as 1...meaning
it will always paste in cell E1 + 1, which is cell E2.

The following example:


Code:
--------------------

Sub lrnLoop()

x = Range("C65536").End(xlUp).Row
y = Range("E65536").End(xlUp).Row

For Each i In Range("C1:C" & x)
i.Copy Range("E" & i)
Next i

End Sub
--------------------


uses i instead of y, which adds 1 cell each time it loops....starting
in cell E1...

I'm sure I've messed up the explanation of what's happening in the
code.....



But I've tried.......

Hope this helps...

Dave M.

 

[Chuck W]

You are correct when you say "xlup takes the cursor from cell in row 65536

Don Here is my code:

' testanswer Macro
' Macro recorded 9/7/2003 by
'
' Keyboard Shortcut: Ctrl+m
'
x = Range("c65536").End(xlUp).Row
y = Range("e65536").End(xlUp).Row + 1

For Each c In Range("c1:c" & x)
c.Copy Range("e" & y)
Next

Chuck

 

[Harlan Grove]

...
...

x = Range("c65536").End(xlUp).Row
y = Range("e65536").End(xlUp).Row + 1

For Each c In Range("c1:c" & x)
c.Copy Range("e" & y)
Next

...

Unless the variable y is incremented inside the For loop, this is equivalent to
copying only the bottommost nonblank cell in column C to the cell immediately
below the bottommost cell in column E. Try

x = Range("c65536").End(xlUp).Row
y = Range("e65536").End(xlUp).Row + 1

For Each c In Range("c1:c" & x)
c.Copy Range("e" & y)
y = y + 2
Next

to copy nonblank cells in column C to cells with intervening blank cells in
column E.

 

[Chuck W]

Harlan,
I need help in one more area which is very similar. I have a list of
items (about 50) in column A. starting in A2. I have a 2nd list in
Column C starting in C3 with 2 blank cells before the next entry.
Otherwords the data is in cells c3,c6 ,c9 etc. I want to Cut the first
item from column A and paste it into C2, paste the next one on C5 etc.
Just like before I get the cut part right but not the paste all going
to one cell. I know that I must add cell +3 to c2 but I'm not sure of
the code.

I tried making the range "C" & i but that did not work. Can you help
I guess that I do not understand the I should define i and if I also
need an y or x. Like I said this is all new.
Thanks,
Chuck




Below is the part of the code that does seem to work.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/11/2003 by
'

'
Range("$A$1").Select
Selection.End(xlDown).Select
Selection.Cut
Range("C2").Select
ActiveSheet.Paste
Application.CutCopyMode = False


End Sub

 

[Chuck W]

Harlan, Everything works fine. Thank you. Can I ask you some
questions about the code? I'm trying to learn as well as solve a
problem.

1. The .row in the first 3 lines does that mean the range is the whole
second row and not just the cell?
2. What does long mean in the Dim statement mean?
3. What does pedantic mean? Dictionary says hanging on, I thought it
was just a definitiom.
4. In the destination line what does the 1 afterthe i, mean or do?



Thanks again for your patience.

Chuck

 

[Alan Beban]

Chuck W wrote:[snip]

3. What does pedantic mean? Dictionary says hanging on, I thought it
was just a definitiom.

pedantic, not pendantic.

Alan Beban

 

[Harlan Grove]

...
...

1. The .row in the first 3 lines does that mean the range is the whole
second row and not just the cell?

No. The .Row property of the Range class is the row number of it topmost cells.
So [X99:Z200].Row would be 99.

2. What does long mean in the Dim statement mean?

It means I'm odd and always use 32-bit integers. There are 3 integer types
provided in VB[A]: Byte, Integer and Long. Respectively, they take up 8, 16 and
32 binary bits each. In theory smaller integer types should consume less memory.
In practice (and I don't know the VB[A] specifics - I'm just guessing) many
programming languages don't pack integer types in the fewest possible bytes, but
tend to align their base addesses on convenient boundaries. On 32-bit hardware,
that'd be 32-bit, or 4-byte, boundaries.

Anyway, Dim n As Long declares the variable n to be a 32-bit (long) integer,
which in VB[A] is termed a 'Long'.

3. What does pedantic mean? Dictionary says hanging on, I thought it
was just a definitiom.

In my case, using too many words to make a minor point. You don't need the 3
assignment statements to which the comment applied, but they're there in case
you (or any other reader) ever parametrize this procedure.

4. In the destination line what does the 1 afterthe i, mean or do?

The underscore on the line above is a statement continuation character, so both
lines together are one statement. The Destination is a named parameter of the
Range class's .Cut method which specifies the range to which the cut range
should be moved. [X].Cut Destination:=[Y] is the same as selecting range X, Edit

 

[Chuck W]

Harlan, Thanks for taking so much time to answer my questions.

Chuck

...
..

1. The .row in the first 3 lines does that mean the range is the whole
second row and not just the cell?

No. The .Row property of the Range class is the row number of it topmost cells.
So [X99:Z200].Row would be 99.

2. What does long mean in the Dim statement mean?

It means I'm odd and always use 32-bit integers. There are 3 integer types
provided in VB[A]: Byte, Integer and Long. Respectively, they take up 8, 16 and
32 binary bits each. In theory smaller integer types should consume less memory.
In practice (and I don't know the VB[A] specifics - I'm just guessing) many
programming languages don't pack integer types in the fewest possible bytes, but
tend to align their base addesses on convenient boundaries. On 32-bit hardware,
that'd be 32-bit, or 4-byte, boundaries.

Anyway, Dim n As Long declares the variable n to be a 32-bit (long) integer,
which in VB[A] is termed a 'Long'.

3. What does pedantic mean? Dictionary says hanging on, I thought it
was just a definitiom.

In my case, using too many words to make a minor point. You don't need the 3
assignment statements to which the comment applied, but they're there in case
you (or any other reader) ever parametrize this procedure.

4. In the destination line what does the 1 afterthe i, mean or do?

The underscore on the line above is a statement continuation character, so both
lines together are one statement. The Destination is a named parameter of the
Range class's .Cut method which specifies the range to which the cut range
should be moved. [X].Cut Destination:=[Y] is the same as selecting range X, Edit

Cut, selecting range Y, Edit > Paste, then returning to the range selected and the cell active prior to selecting range X.