Sumproduct VBA

[tanalea]

Hi all,

I'm a VBA newbie, using Excel 2010. I'm working on a macro and need to place sumproduct in a series of cells.

I have a set of data that has been created with the header row starting on row 4. The sumproduct formula will first be entered into cell X3. I need to sumproduct all of the data in column V with column X starting in row 5 through the end of the data.

I need a similar formula all of the cells from X3 through AM3. In each formula, the first array will be the column V data and the second array will be the current column's data.

i.e. for X3, the formula will be sumproduct(V5:V..., X5:X...); for Z3, the formula will be sumproduct(V5:V..., Z5:Z...)

Each time the macro is run, there will be a different number of rows in the data so I can't refer to the specific range. I've tried to figure out how to use xlUp to identify the range but to no avail.

Any help would be much appreciated!

 

[pplstuff]

To find the last row:

cells(rows.count, 24).end(xlup).row

If the formulas are all using the same logic, just use absolute refernce and copy the formula over the next X columns.

with range("X3")
.formula ="=sumproduct($V$5:$V" & cells(rows.count, 24).end(xlup).row & ",X5:X" & cells(rows.count, 24).end(xlup).row & ")"
end with

If all the columns are different lengths, you'll want to loop through each column and enter the formulas individually.

 

[tanalea]

That works perfectly!! Thanks!