PDA

View Full Version : Solved: sum only certain columns



lneilson
08-13-2008, 01:43 PM
Hi

how can I adjust this formula so it sums every row but only from the columns labled Sun. (the label is in the row 83) with the answer displayed in the respective row in the last column with data +1

that was the problem below is the solution thanks to XLD for the code
and Aussiebear for translating

thanks again

lneilson

'Get the last used row within the collection of used columns.
lngRowLast = Cells(Rows.Count, "A").End(xlUp).Row
'Get the last used column.
intColLast = Cells(lngRowLast, Columns.Count).End(xlToLeft).Column
'adjust to remove footer rows
lngRowLast = lngRowLast - 2

Cells(1, intColLast + 1).Resize(lngRowLast).FormulaR1C1 = "=SUM(RC1:RC" & intColLast & ")"
Cells(lngRowLast + 1, intColLast + 2).Resize(, 7).Value = Array("Sun.", "Mon.", "Tue.", "Wed.", "Thu.", "Fri.", "Sat.", "Sun.")
Cells(1, intColLast + 2).Resize(lngRowLast, 7).FormulaR1C1 = _
"=SUMIF(R" & lngRowLast + 1 & "C1:R" & lngRowLast + 1 & "C" & intColLast & ",R" & _
lngRowLast + 1 & "C," & "RC1:RC" & intColLast & ")"
Sheets("Sheet3").Select

Aussiebear
08-14-2008, 02:35 AM
Louie, The problem here was simply that the description of the issue was so vague, that any initial interest in assisting was quickly diminished.

Its one thing to ask for code, but in reality, if & when you have a problem, try to write down a process in logical order of what needs to happen, in the order it needs to happen. When you are happy with that process, then the code provision is so much easier. Anyway ,this has been a great learning opportunity.

By the way, thank you for making the effort to thank XLD, for his assistance. I too would like to endorse your appreciation to Bob.