I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?

I am running Excel 2003

Why not just sum from column D to column IV (i.e. D4:IV4). Any empty
cells will be treated a zero.



"lesley1000" wrote:

I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?

Look at this site:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Look at the example after #7.
It reads "You can also change the Columns..........."

Regards,
Ryan---

--
RyGuy


"Flick Olmsford" wrote:

I am running Excel 2003

Why not just sum from column D to column IV (i.e. D4:IV4). Any empty
cells will be treated a zero.



"lesley1000" wrote:

I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?

I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing
this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run
the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it
will
do a fixed number of columns. Any suggestions?

You mentioned running a macro, so I assume you need the described
functionality for use in a macro that is doing more than just summing up
these columns (otherwise it would be more efficient to get the sum at the
spreadsheet level than from VBA code). If that is the case, you can use this
function to sum the columns from Column D onward. Any blank cells, or cells
containing text that cannot be converted to a numeric value, will be skipped
over. Simply pass in the Row number you want to perform the summation for.

Function SumColumns(RowNumber As Long) As Double
Dim X As Long
Dim LastColumnInRow As Long
LastColumnInRow = ActiveSheet.Cells(RowNumber, Columns.Count). _
End(xlToLeft).Column
For X = 4 To LastColumnInRow
If IsNumeric(Cells(RowNumber, X)) Then
SumColumns = SumColumns + Cells(RowNumber, X).Value
End If
Next
End Function

Rick

Cannot do that because it'll be a circular formula - as the formula will be
at the end of the row of cells I want to sum

Flick Olmsford wrote:
I am running Excel 2003

Why not just sum from column D to column IV (i.e. D4:IV4). Any empty
cells will be treated a zero.

I put into Excel weekly figures (there change each week and time I put them
in). I want to do a sum of the row at the end to generate a total. Doing this
in excel is fine, but I want the code to sum from column D (this is fixed
each week) until the last column, which will be different each time I run the
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200712/1

Going to have a go at ryguy7272 and rick's ideas - thank you

ryguy7272 wrote:
Look at this site:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Look at the example after #7.
It reads "You can also change the Columns..........."

Regards,
Ryan---

I am running Excel 2003

[quoted text clipped - 7 lines]
macro. I've tried naming the cells, but VBA doesn't do it correctly, it will
do a fixed number of columns. Any suggestions?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200712/1