Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through one range to fill another
Hi there,
I have 7 sheets. Sheet 1 is a registration sheet, name of person goes in C7. A number is allocated to that person (by wks function) in B7. Sheets, 2,3,4 & 7 then need to be filled in from this information, into cols A & B. I can do this by using source range and dest range to copy it over after the list of names is completed, but: on all of the sheets, I have a line of wks coding that needs to be extended down for each of the names listed. What I need to be able to express is: For each cell in wks 1, range C7:C67 that is not empty,(has a name in it), on sheets 2,3 , 4 & 7 extend the line of coding down from A7:AI7 for however many lines are needed, (this is never more than 60), then place the value of wks 1,Cells B & C into sheets 2,3 4&7 in columns A and B. I have tried numerous ways and not managing to get it into the right place. This is how it should look. Sheet 1 A B C 7 101 Jack 102 jill 103 joan 104 jim 105 jenny Sheets 2,3 4 & 7 A B C===============================AI 7 101 Jack above line copied down to match each entry (formula) 102 jill above line copied down to match each entry (formula) 103 joan above line copied down to match each entry (formula) 104 jim above line copied down to match each entry (formula) 105 jenny above line copied down to match each entry (formula) Sheet 5 also needs the information filling in but there are 7 rows per person involved. So the details need to be in cols A & B row 13/20/27/34 etc.(formula range C7:AF13). Sheet 6 as above but with 8 rows per person €“ 14/22/30/38 etc., (formula range C7: AK14) Both sheets also need the wks formula extending down This project was put together as a first effort and I am reviewing it because it is so heavy with wks formulas and functions and each sheet at the moment carries the full coding for up to 60 entries. It works very well and does what it needs to do, but I want to learn how to use loops etc to make the project lighter and it would be much more efficient if I could only carry the one line of coding for each sheet which would extend as necessary depending on how many names were entered on the registration (sheet 1) sheet. My last question. At the moment, sheets 5 & 6 carry 2 macros in col C which €˜open the card,& close the card. This leaves all the other sets of 7 or 8 rows just showing the name details and a macro to open them with. Is there any way of applying these macros only if the rows have name details in. Finally, is there a book that explains different ways of doing these things that would be suitable for enthusiastic learning ? but keeping it understandable. Any help or advice appreciated. Thanks Sybs |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through one range to fill another
Way too much to ask at one time. Here is some example code to help you set a
range, loop through it, and copy it, to get you started: Option Explicit Sub test() Dim LRow As Long Dim rng As Range, c As Range Dim rng2 As Range 'Finds the last filled row in column 3 LRow = Cells(Rows.Count, 3).End(xlUp).Row 'or LRow = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range("B7:C" & LRow) For Each c In rng 'do something Next LRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row Set rng2 = Worksheets("Sheet2").Range("A2:B" & LRow) For Each c In rng2 'do something Next 'copy rng to next empty row in sheet2 rng.Copy Worksheets("Sheet2").Range("A" & LRow + 1) End Sub Mike F "SYBS" wrote in message ... Hi there, I have 7 sheets. Sheet 1 is a registration sheet, name of person goes in C7. A number is allocated to that person (by wks function) in B7. Sheets, 2,3,4 & 7 then need to be filled in from this information, into cols A & B. I can do this by using source range and dest range to copy it over after the list of names is completed, but: on all of the sheets, I have a line of wks coding that needs to be extended down for each of the names listed. What I need to be able to express is: For each cell in wks 1, range C7:C67 that is not empty,(has a name in it), on sheets 2,3 , 4 & 7 extend the line of coding down from A7:AI7 for however many lines are needed, (this is never more than 60), then place the value of wks 1,Cells B & C into sheets 2,3 4&7 in columns A and B. I have tried numerous ways and not managing to get it into the right place. This is how it should look. Sheet 1 A B C 7 101 Jack 102 jill 103 joan 104 jim 105 jenny Sheets 2,3 4 & 7 A B C===============================AI 7 101 Jack above line copied down to match each entry (formula) 102 jill above line copied down to match each entry (formula) 103 joan above line copied down to match each entry (formula) 104 jim above line copied down to match each entry (formula) 105 jenny above line copied down to match each entry (formula) Sheet 5 also needs the information filling in but there are 7 rows per person involved. So the details need to be in cols A & B row 13/20/27/34 etc.(formula range C7:AF13). Sheet 6 as above but with 8 rows per person - 14/22/30/38 etc., (formula range C7: AK14) Both sheets also need the wks formula extending down This project was put together as a first effort and I am reviewing it because it is so heavy with wks formulas and functions and each sheet at the moment carries the full coding for up to 60 entries. It works very well and does what it needs to do, but I want to learn how to use loops etc to make the project lighter and it would be much more efficient if I could only carry the one line of coding for each sheet which would extend as necessary depending on how many names were entered on the registration (sheet 1) sheet. My last question. At the moment, sheets 5 & 6 carry 2 macros in col C which 'open the card,& close the card'. This leaves all the other sets of 7 or 8 rows just showing the name details and a macro to open them with. Is there any way of applying these macros only if the rows have name details in. Finally, is there a book that explains different ways of doing these things that would be suitable for enthusiastic learning ? but keeping it understandable. Any help or advice appreciated. Thanks Sybs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through one range to fill another
Thanks very much for that help Mike. I didnt expect anyone to have the time
to go through it all, but exactly what I was hoping to achieve eventually, I thought better to set out for clarity. Getting started with looping is a great help. Thanks. its just what I need to have an idea of how to go about it. "Mike Fogleman" wrote: Way too much to ask at one time. Here is some example code to help you set a range, loop through it, and copy it, to get you started: Option Explicit Sub test() Dim LRow As Long Dim rng As Range, c As Range Dim rng2 As Range 'Finds the last filled row in column 3 LRow = Cells(Rows.Count, 3).End(xlUp).Row 'or LRow = Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range("B7:C" & LRow) For Each c In rng 'do something Next LRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row Set rng2 = Worksheets("Sheet2").Range("A2:B" & LRow) For Each c In rng2 'do something Next 'copy rng to next empty row in sheet2 rng.Copy Worksheets("Sheet2").Range("A" & LRow + 1) End Sub Mike F "SYBS" wrote in message ... Hi there, I have 7 sheets. Sheet 1 is a registration sheet, name of person goes in C7. A number is allocated to that person (by wks function) in B7. Sheets, 2,3,4 & 7 then need to be filled in from this information, into cols A & B. I can do this by using source range and dest range to copy it over after the list of names is completed, but: on all of the sheets, I have a line of wks coding that needs to be extended down for each of the names listed. What I need to be able to express is: For each cell in wks 1, range C7:C67 that is not empty,(has a name in it), on sheets 2,3 , 4 & 7 extend the line of coding down from A7:AI7 for however many lines are needed, (this is never more than 60), then place the value of wks 1,Cells B & C into sheets 2,3 4&7 in columns A and B. I have tried numerous ways and not managing to get it into the right place. This is how it should look. Sheet 1 A B C 7 101 Jack 102 jill 103 joan 104 jim 105 jenny Sheets 2,3 4 & 7 A B C===============================AI 7 101 Jack above line copied down to match each entry (formula) 102 jill above line copied down to match each entry (formula) 103 joan above line copied down to match each entry (formula) 104 jim above line copied down to match each entry (formula) 105 jenny above line copied down to match each entry (formula) Sheet 5 also needs the information filling in but there are 7 rows per person involved. So the details need to be in cols A & B row 13/20/27/34 etc.(formula range C7:AF13). Sheet 6 as above but with 8 rows per person - 14/22/30/38 etc., (formula range C7: AK14) Both sheets also need the wks formula extending down This project was put together as a first effort and I am reviewing it because it is so heavy with wks formulas and functions and each sheet at the moment carries the full coding for up to 60 entries. It works very well and does what it needs to do, but I want to learn how to use loops etc to make the project lighter and it would be much more efficient if I could only carry the one line of coding for each sheet which would extend as necessary depending on how many names were entered on the registration (sheet 1) sheet. My last question. At the moment, sheets 5 & 6 carry 2 macros in col C which 'open the card,& close the card'. This leaves all the other sets of 7 or 8 rows just showing the name details and a macro to open them with. Is there any way of applying these macros only if the rows have name details in. Finally, is there a book that explains different ways of doing these things that would be suitable for enthusiastic learning ? but keeping it understandable. Any help or advice appreciated. Thanks Sybs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Range Based On Certain Criteria? Possible looping through the Range | Excel Programming | |||
Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
Looping Range | Excel Programming | |||
Looping to fill ListBox | Excel Programming | |||
looping through a range | Excel Programming |