Loop to specific rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Omar - 26 Sep 2007 14:52 GMT

I need to write a macro that copies cells in a specific row and then
loops to the next row, next row being 5 rows below.

So, copy cells C7, D7 to H7
then, Copy the next set C12, D12 ... H12
and so on.

I would assume this would be done using 2 loops, one for goings
through the columns and one for the rows, but I could be wrong.

I am not sure of the syntax, so if anyone can give me some tips, that
would be great.
Thanks.

Reply to this Message

HI - 26 Sep 2007 15:01 GMT

Don't know if this is what you wanted. Following macro will add number
1 on cells C7, D7 to H7 and C12, D12 ... H12 etc.
You replace the 1 with your formula

Sub TEST()
Range("C7").Select
For i = 0 To 10
For j = 0 To 5
ActiveCell.Offset(i * 5, j).Value = 1
Next j
Next i
End Sub

> I need to write a macro that copies cells in a specific row and then
> loops to the next row, next row being 5 rows below.
[quoted text clipped - 9 lines]
> would be great.
> Thanks.

Reply to this Message

Omar - 26 Sep 2007 15:15 GMT

> Don't know if this is what you wanted. Following macro will add number
> 1 on cells C7, D7 to H7 and C12, D12 ... H12 etc.
[quoted text clipped - 8 lines]
> Next i
> End Sub

Thanks, that makes sense.

I had another question
my original formula was just extracting the first 3 chars from a
specific cell, now as I have to loop through the columns and rows, the
following won't work:
id = Range("C7").Characters(1, 3).Text

Using you method, how would I integrate this into the code?

ActiveCell.Offset(i * 5, j).Value = 1 is inserting '1' into those
cells. How do I make it copy those cells instead?

Reply to this Message

Omar - 26 Sep 2007 15:57 GMT

I am tying the following, but it doesn't seem to work

Range("B7").Select
For i = 0 To 10
For j = 0 To 6
ActiveCell.Offset(i * 5, j).Select
Selection.Copy

Windows("Test.xls").Activate
ActiveCell.Range("A2").Select
ActiveSheet.Paste

Next j
Next i

Reply to this Message

Don Guillett - 26 Sep 2007 16:21 GMT

Run from the sheet with the source data and change sheet27 to suit
sub copyevery7()
With Sheets("sheet27")
.Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I am tying the following, but it doesn't seem to work
>
[quoted text clipped - 10 lines]
> Next j
> Next i

Reply to this Message

Don Guillett - 26 Sep 2007 16:29 GMT

Didn't notice the part about another file. Try this from the source workbook
& sheet if BOTH open.

Sub copyevery7()
With Workbooks("test.xls").Sheets("sheet1")
.Columns("H:z").Delete
For i = 7 To Cells(Rows.Count, "c").End(xlUp).Row Step 7
lr = .Cells(Rows.Count, "H").End(xlUp).Row + 1
Cells(i, "c").Resize(, 4).Copy .Cells(lr, "h")
Next i
End With
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Run from the sheet with the source data and change sheet27 to suit
> sub copyevery7()
[quoted text clipped - 21 lines]
>> Next j
>> Next i

Reply to this Message

Omar - 26 Sep 2007 16:48 GMT

When I use this, the values that are being copied are for some reason
pasted in cell H2, I2...etc
What does the Resize function do?

Any ideas?

> Didn't notice the part about another file. Try this from the source workbook
> & sheet if BOTH open.
[quoted text clipped - 43 lines]
> >> Next j
> >> Next i

Reply to this Message

Don Guillett - 26 Sep 2007 17:23 GMT

From this data in the source workbook
aaa bbb ccc ddd
a 1 2 3
b 2 3 4
c 3 4 5
d 4 5 6
e 5 6 7
f7 6 7 8
g 7 8 9
h 8 9 10
i 9 10 11
j 10 11 12
k 11 12 13
l 12 13 14
m7 13 14 15
n 14 15 16
o 15 16 17
p 16 17 18
q 17 18 19
r 18 19 20
s 19 20 21
t7 20 21 22
u 21 22 23
v 22 23 24
w 23 24 25
x 24 25 26
y 25 26 27

You should have in Test.xls
f7 6 7 8
m7 13 14 15
t7 20 21 22

Is that what you wanted?

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> When I use this, the values that are being copied are for some reason
> pasted in cell H2, I2...etc
[quoted text clipped - 39 lines]
>> > Microsoft MVP Excel
>> > SalesAid Software

Reply to this Message

HI - 26 Sep 2007 17:28 GMT

Sorry Omar did not mean to leave you hanging. I don't really
understand what you meant, but for your question for me how to make my
code copy... Now I must warn you this is not the best way... well
almost anything but here goes. This macro will copy values from cells
C7 etc to Test.xls to column A so that first value (C7) will go to A2
and D7 in A3 and so on. I asume you will have both original data.xls
and test.xls open and nothing else.

Sub test()
Range("c7").Select
For i = 0 To 10
For j = 0 To 6
ActiveCell.Offset(i * 5, j).Copy

Windows("Test.xls").Activate
Range("A" & 2 + k).Select
Selection.PasteSpecial Paste:=xlValues
ActiveWindow.ActivateNext
k = k + 1
Next j
Next i
End Sub

hope this helps

Reply to this Message

Omar - 26 Sep 2007 18:05 GMT

Don, yes that is what I wanted, I got it working now somewhat.

"HI", Sorry for not being clear, what I have is this.
Original.xls:

Row 1 User Monday Tuesday
Row 7 jack 123/45 876/54
Row12 john 526/4 456/3
Row17 Bob 231/e 314/tw

what I have to do is extract the user and id field and put it in
another xls.
I figured out how to do this without the loop. with the loop i was
having some trouble.

Another complication is that, from the id field, i just need to pull
the first three chars.so at the end the new file will look something
like:

Row 1 User ID
Row 2 jack 123
Row 3 john 526

I'll try playing around with your code and see if I can come up with
anything.

> Sorry Omar did not mean to leave you hanging. I don't really
> understand what you meant, but for your question for me how to make my
[quoted text clipped - 20 lines]
>
> hope this helps

Reply to this Message

Don Guillett - 26 Sep 2007 19:37 GMT

Why do you keep changing your desires with each post. You really need to
learn to ask for WHAT YOU WANT the first time. You will need to use
something like

Cells(i, "c").Copy .Cells(lr, "h")
left(Cells(i, "d"),3).Copy .Cells(lr, "i")

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Don, yes that is what I wanted, I got it working now somewhat.
>
[quoted text clipped - 46 lines]
>>
>> hope this helps

Reply to this Message

Don Guillett - 26 Sep 2007 15:03 GMT

for i = 7 to whatever step 5
cells(i,"c").resize(,2) copy cells(i,"h")
next i

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I need to write a macro that copies cells in a specific row and then
> loops to the next row, next row being 5 rows below.
[quoted text clipped - 9 lines]
> would be great.
> Thanks.

Reply to this Message