need help with nested for-next loops  
Author Message
shigrl





PostPosted: Mon Sep 17 13:16:03 PDT 2007 Top

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

Excel55  
 
 
Joel





PostPosted: Mon Sep 17 13:16:03 PDT 2007 Top

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





> 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
 
 
rpw





PostPosted: Mon Sep 17 14:48:00 PDT 2007 Top

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
>
>
>

>
> > 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
 
 
rpw





PostPosted: Mon Sep 17 16:18:01 PDT 2007 Top

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




> 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
>
>
>

>
> > 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
 
 
rpw





PostPosted: Mon Sep 17 16:24:00 PDT 2007 Top

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




> 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
>
>
>

>
> > 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
 
 
Joel





PostPosted: Mon Sep 17 19:38:01 PDT 2007 Top

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



> 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
>
>

>
> > 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
> >
> >
> >

> >
> > > 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
 
 
rpw





PostPosted: Tue Sep 18 08:38:03 PDT 2007 Top

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




> 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
>

>
> > 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
> >
> >

> >
> > > 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
> > >
> > >
> > >

> > >
> > > > 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