Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct with variable array
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" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProduct with variable array
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" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with a variable range | Excel Worksheet Functions | |||
sumproduct - reading a variable value | Excel Discussion (Misc queries) | |||
how can I see if an array contain a certain variable? | Excel Programming | |||
about ARRAY variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |