SumProduct with variable array

[Guest]

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

 

[Tom Ogilvy]

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)

 

[Guest]

Thanks Tom - this works great!!

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)