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
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