Help Please

[Ant Nutting]

Hope someone can help, me problem is this:
In Sheet 1, i have columns A to L, which have information in them. In sheet
2 i need to get the sum of the contents of columns, starting with A. However
i would like the qty of columns to be regulated by the figure i put in Sheet
2 cell A1. Here's an example:
If SHEET 2 CELL A1 = 3, then i would like Sum of SHEET 1 Column A to C.
OR
If SHEET 2 CELL A1 = 7, then i would like Sum of SHEET 1 Column A to G.
I hope someone can understand this and is able to help me.
Thx
Ant

 

[Guest]

You can probably just use a nested loop. I don't know how your data is on
sheet1 but here's a quickie, you'll need to replace X with the length of the
longest column in sheet1. It'll output the total to C1 on sheet2.

intRowLength = X
intColumnLength = Val(Sheets("Sheet2").Cells(1, 1).Value)

dblTotal = 0
For intCounterA = 1 To intColumnLength
For intCounterB = 1 To intRowLength
dblTotal = dblTotal + Val(Sheets("Sheet1").Cells _(intCounterB,
intCounterA).Value)
Next
Next

Sheets("Sheet2").Cells(1, 3).Value = dblTotal

 

[Bob Phillips]

=SUM(INDIRECT("Sheet1!A1:"&CHAR(A1+64)&"1"))

--

HTH

RP
(remove nothere from the email address if mailing direct)