SumProduct with variable array

I need to calculate the sum of A1*A10 on all sheets in a workbook except for
Sheet 1 - ie Sheet2!a1*Sheet2!a10 + Sheet3!a1*Sheet3!a10 etc. I currently
have 4 sheets in the workbook + my code looks like this:

A = Array(Sheets(2).Range("a1").Value, Sheets(3).Range("a1").Value,
Sheets(4).Range("a1").Value)
B = Array(Sheets(2).Range("a10").Value, Sheets(3).Range("a10").Value,
Sheets(4).Range("a10").Value)
answer = Application.WorksheetFunction.SumProduct(A, B)

The problem is that the number of sheets in the workbook will vary over time
- how can I incorporate ActiveWorkbook.Sheets.Count or some other code to
have it calculate the answer for the current # of sheets in the workbook? Any
suggestions most welcome!!

Dim A() as Double
Dim B() as Double
Dim i as Long
Dim answer as Double
redim A(2 to worksheets.count)
redim B(2 to worksheets.count)
for i = 2 to worksheets.count
A(i) = worksheets(i).Range("A1").Value
B(i) = worksheets(i).Range("A10").Value
Next
answer = Application.SumProduct(A,B)

--
Regards,
Tom Ogilvy


"Lynn" <[email protected]> wrote in message
news:[email protected]...
> I need to calculate the sum of A1*A10 on all sheets in a workbook except
for
> Sheet 1 - ie Sheet2!a1*Sheet2!a10 + Sheet3!a1*Sheet3!a10 etc. I currently
> have 4 sheets in the workbook + my code looks like this:
>
> A = Array(Sheets(2).Range("a1").Value, Sheets(3).Range("a1").Value,
> Sheets(4).Range("a1").Value)
> B = Array(Sheets(2).Range("a10").Value, Sheets(3).Range("a10").Value,
> Sheets(4).Range("a10").Value)
> answer = Application.WorksheetFunction.SumProduct(A, B)
>
> The problem is that the number of sheets in the workbook will vary over
time
> - how can I incorporate ActiveWorkbook.Sheets.Count or some other code to
> have it calculate the answer for the current # of sheets in the workbook?
Any
> suggestions most welcome!!

Thanks Tom - this works great!!

"Tom Ogilvy" wrote:

> Dim A() as Double
> Dim B() as Double
> Dim i as Long
> Dim answer as Double
> redim A(2 to worksheets.count)
> redim B(2 to worksheets.count)
> for i = 2 to worksheets.count
> A(i) = worksheets(i).Range("A1").Value
> B(i) = worksheets(i).Range("A10").Value
> Next
> answer = Application.SumProduct(A,B)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Lynn" <[email protected]> wrote in message
> news:[email protected]...
> > I need to calculate the sum of A1*A10 on all sheets in a workbook except
> for
> > Sheet 1 - ie Sheet2!a1*Sheet2!a10 + Sheet3!a1*Sheet3!a10 etc. I currently
> > have 4 sheets in the workbook + my code looks like this:
> >
> > A = Array(Sheets(2).Range("a1").Value, Sheets(3).Range("a1").Value,
> > Sheets(4).Range("a1").Value)
> > B = Array(Sheets(2).Range("a10").Value, Sheets(3).Range("a10").Value,
> > Sheets(4).Range("a10").Value)
> > answer = Application.WorksheetFunction.SumProduct(A, B)
> >
> > The problem is that the number of sheets in the workbook will vary over
> time
> > - how can I incorporate ActiveWorkbook.Sheets.Count or some other code to
> > have it calculate the answer for the current # of sheets in the workbook?
> Any
> > suggestions most welcome!!
>
>
>