Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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 On 26 syys, 16:52, Omar wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
for i = 7 to whatever step 5 cells(i,"c").resize(,2) copy cells(i,"h") next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Omar" wrote in message oups.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. 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
On Sep 26, 10:01 am, HI wrote:
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 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Omar" wrote in message ps.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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? On Sep 26, 11:29 am, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Omar" wrote in message ups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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? -- Don Guillett Microsoft MVP Excel SalesAid Software "Omar" wrote in message ups.com... 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? On Sep 26, 11:29 am, "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 -- Don Guillett Microsoft MVP Excel SalesAid Software |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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. On Sep 26, 12:28 pm, HI wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop to specific rows
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") -- Don Guillett Microsoft MVP Excel SalesAid Software "Omar" wrote in message ups.com... 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. On Sep 26, 12:28 pm, HI wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Loop thru lines of text in a cell and extract specific values: please help? | Excel Programming | |||
Exclude specific wks in FOR EACH wks Loop | Excel Programming | |||
Loop through specific worksheets | Excel Programming | |||
Macro/Loop If Statement Help -delete the row with the specific te | Excel Programming |