Sum to last column in loop

[efarkouh]

Having a hard time figuring this out.

Here's what I have so far:

Please Login or Register  to view this content.

The sum function will always start at D2, but will continue until the last column (eg until P2). I wanted to make it a more complex function but I couldn't figure out how to get it to reach the last column on the same row.

I tried to use

Please Login or Register  to view this content.

But that made an absolute reference at cell D2 (so the last cell in the "Utilization" column had =sum($D$2:P52) which is not what I'm looking for.

Thoughts?

[Leith Ross]

Hello efarkouh,

Are you trying to convert the formula into a VBA macro?

[efarkouh]

Hello efarkouh,

Are you trying to convert the formula into a VBA macro?

Yes, I'd like it to be part of my macro.

In the last column of each of sheets 2-4, I would like there to be a column at the end of the sheet in which I can have the sum of D2 until the last column (eg D2:P2) (the number of columns varies by sheet) divided by the count of columns (except for the last one where the formula is).

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[efarkouh]

Maybe:

Please Login or Register  to view this content.

Thanks for the reply, John.

I tried this:

Please Login or Register  to view this content.

What happened was that it used an absolute reference for everything and only for the range of one of the sheets; it looked like this:

=sum($D$2:$P$2)

for every cell in the column "Utilization" in all the sheets.

[efarkouh]

Changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

fixes the issue where it wasn't summing a dynamic number of columns, but the references are still absolute.

Utilization column:

Sheet 2: =SUM($D$2:$V$2)
Sheet 3: =SUM($D$2:$I$2)
Sheet 4: =SUM($D$2:$P$2)

[JOHN H. DAVIS]

I think you'd need this instead:

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

If the references are an issue add the following too your code:

Please Login or Register  to view this content.

[efarkouh]

Norie,

Thanks for that. That got rid of the absolutes.

John,

That last one is an important tip. Thanks a lot.