Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Hi all,
I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Sub getolddata()
Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Thanks for the quick reply Joel. I am trying to understand this and am not
sure that I do. "Next Myweek" is in three places. The first "Next Myweek", I don't understand why loop 52 times - is this clearing out any previous SumArray(Myweek) by setting them to 0? The second "Next Myweek" gets a runtime error # 13 Type mis-match. The third place "Next Myweek" is used looks like it is being used to offset the column and give the cell the value of SumArray(Myweek). For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Aha! I found the error in the spreadsheet formula - if there was no entry
then a "" was entered otherwise a number was calculated. VB can't sum "". So now I get the code and it works! Thanks for your help in this. -- rpw "Joel" wrote: Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Correction: It works, but not like I thought it would. The cell range being
summed is not skipping from H17 to H20 nor from H32 to H36. -- rpw "Joel" wrote: Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
I made a small change and now it works. Sorry I didn't look carefully at all
the data when I originally tested the code. the following statement reads 52 values from the old worksheet skipping cells as requested Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48,H52:H64") The code takes 52 weeks of data in one worksheet and puts it in an array SumArray. Then goes to next worksheet and add the 52 weeks of data to the SumAray. The macro keeps doing this for all worksheets. Then the macro write the final array back in the new worksheet. Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For MyWeek = 1 To 52 SumArray(MyWeek) = 0 Next MyWeek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48,H52:H64") MyWeek = 1 For Each cell In OldRange SumArray(MyWeek) = SumArray(MyWeek) + _ cell.Value MyWeek = MyWeek + 1 Next cell Next ws For MyWeek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, MyWeek - 1) = _ SumArray(MyWeek) Next MyWeek Workbooks(OldBkName).Close End Sub "rpw" wrote: Correction: It works, but not like I thought it would. The cell range being summed is not skipping from H17 to H20 nor from H32 to H36. -- rpw "Joel" wrote: Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help with nested for-next loops
Hi Joel,
Thanks again for the help and thanks also for the explanation. This version is working as expected and I can now see how each sum is held in memory until it is posted in the current workbook. Thanks, I've learned a bit more today! :-) -- rpw "Joel" wrote: I made a small change and now it works. Sorry I didn't look carefully at all the data when I originally tested the code. the following statement reads 52 values from the old worksheet skipping cells as requested Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48,H52:H64") The code takes 52 weeks of data in one worksheet and puts it in an array SumArray. Then goes to next worksheet and add the 52 weeks of data to the SumAray. The macro keeps doing this for all worksheets. Then the macro write the final array back in the new worksheet. Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For MyWeek = 1 To 52 SumArray(MyWeek) = 0 Next MyWeek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48,H52:H64") MyWeek = 1 For Each cell In OldRange SumArray(MyWeek) = SumArray(MyWeek) + _ cell.Value MyWeek = MyWeek + 1 Next cell Next ws For MyWeek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, MyWeek - 1) = _ SumArray(MyWeek) Next MyWeek Workbooks(OldBkName).Close End Sub "rpw" wrote: Correction: It works, but not like I thought it would. The cell range being summed is not skipping from H17 to H20 nor from H32 to H36. -- rpw "Joel" wrote: Sub getolddata() Const OldBook = "c:\temp\Old Workbook.xls" Workbooks.Open Filename:=OldBook OldBkName = ActiveWorkbook.Name Dim SumArray(52) For Myweek = 1 To 52 SumArray(Myweek) = 0 Next Myweek For Each ws In Worksheets Set OldRange = Sheets(ws.Name). _ Range("H5:H17,H20:H32,H36:H48") For Myweek = 1 To 52 SumArray(Myweek) = SumArray(Myweek) + _ OldRange(Myweek) Next Myweek Next ws For Myweek = 1 To 52 ThisWorkbook.Sheets("Sheet1"). _ Range("B6").Offset(0, Myweek - 1) = _ SumArray(Myweek) Next Myweek Workbooks(OldBkName).Close End Sub "rpw" wrote: Hi all, I have a old workbook with several worksheets. Each sheet is formatted the same having weekly entries grouped quarterly for a 1 year period. (Each row is 1 week). I have a new workbook with each week in columns. I need to have the sum of all week 1 (cell "H5") entries from every worksheet in the old workbook put into the week 1 cell "B6" in the new workbook. And then all week 2 (cell "H6") into week 2 cell "C6" and so on. The data in the old workbook is in four ranges (H5:H17, H20:H32, H36:H48, H52:H64) on each worksheet. The weekly sums go into the new workbook range of "B6:BA6". So, how do I do this? Thanks in advance for any guidance provided rpw |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with nested for loops | Excel Programming | |||
Nested with loops | Excel Programming | |||
nested for loops and end for | Excel Programming | |||
Help on nested loops | Excel Programming | |||
Nested loops?? | Excel Programming |