need help with nested for-next loops

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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!