=?Utf-8?B?cm9kY2hhcg==?=
2/9/2010 8:41:00 PM
Ryan,
Again thanks, have adapted to suit my needs and learnt much from your
experience.
Ta, Rob
"Ryan H" <RyanH@discussions.microsoft.com> wrote in message
news:E049360C-291E-4CE6-A597-16201DB06712@microsoft.com...
> Try this code instead. This worked for me. Its a bit different. Hope
> this
> helps! If so, let me know, click "YES" below.
>
> Sub SubTotal()
>
> Dim MyArray As Variant
> Dim i As Long
> Dim LastRow As Long
> Dim MyFormula As String
>
> MyArray = Array("K", "L", "R", "S", "T", "U")
>
> For i = LBound(MyArray) To UBound(MyArray)
> LastRow = Cells(Rows.Count, MyArray(i)).End(xlUp).Row
> MyFormula = "=SUM(" & MyArray(i) & "2:" & MyArray(i) & LastRow &
> ")"
> Cells(LastRow + 2, MyArray(i)).Formula = MyFormula
> Next i
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Rob" wrote:
>
>> If the last row of data was in K960, I would want the formula to read
>> K2:K960. Row 1 has a header hence the formula starting at K2.
>>
>> Thanks, Rob
>>
>>
>> "Ryan H" <RyanH@discussions.microsoft.com> wrote in message
>> news:2B374AB8-CE40-4870-930C-E73DB8CD18E8@microsoft.com...
>> > What range are you wanting to sum? You may not need to use R1C1
>> > notation
>> > style.
>> > --
>> > Cheers,
>> > Ryan
>> >
>> >
>> > "Rob" wrote:
>> >
>> >> Ryan,
>> >>
>> >> Thanks. I have tried this and whilst I have some errors insomuch that
>> >> the
>> >> formula reads =SUBTOTAL(9,K961:K65356), I will try and figure out
>> >> what's
>> >> happening.
>> >>
>> >> Thanks again, Rob
>> >>
>> >> "Ryan H" <RyanH@discussions.microsoft.com> wrote in message
>> >> news:F4727CFE-3D03-4307-A141-6BA1051A547A@microsoft.com...
>> >> > Give this a try. I basically made an array of columns you wish to
>> >> > put
>> >> > the
>> >> > totals in. Then wrote a loop that loops through the columns in the
>> >> > array,
>> >> > finding the last row, then inserting your formula. Hope this helps!
>> >> > If
>> >> > so,
>> >> > let me know, click "YES" below.
>> >> >
>> >> > Sub SubTotal()
>> >> >
>> >> > Dim MyArray As Variant
>> >> > Dim i As Long
>> >> > Dim InputRow As Long
>> >> >
>> >> > MyArray = Array("K", "L", "R", "S", "T", "U")
>> >> >
>> >> > For i = LBound(MyArray) To UBound(MyArray)
>> >> > InputRow = Cells(Rows.Count, MyArray(i)).End(xlUp).Row + 2
>> >> > Cells(InputRow, MyArray(i)).Formula = "=SUBTOTAL(9,R[-" &
>> >> > InputRow
>> >> > &
>> >> > "]C:R[-1]C)"
>> >> > Next i
>> >> >
>> >> > End Sub
>> >> > --
>> >> > Cheers,
>> >> > Ryan
>> >> >
>> >> >
>> >> > "Rob" wrote:
>> >> >
>> >> >> I have the code below that does what I want but seem untidy and I
>> >> >> feel
>> >> >> it
>> >> >> could be done in a cleaner way. This code runs and in columns K,
>> >> >> L,
>> >> >> R,
>> >> >> S, T
>> >> >> and U it added a formula below the last row of data. As you'll
>> >> >> see,
>> >> >> the
>> >> >> code first finds the last row and RowCount is used in the formula.
>> >> >>
>> >> >> Is there an easier way to achieve what I'm looking for and a way in
>> >> >> which
>> >> >> would be easier to change in the future if needed?
>> >> >>
>> >> >> Thansk, Rob
>> >> >>
>> >> >> Sub Subtotal()
>> >> >> Dim RowCount As Long
>> >> >> Range("K1").Select
>> >> >> Range(Selection, Selection.End(xlDown)).Select
>> >> >> RowCount = Selection.Rows.Count
>> >> >> Range("K1").Select
>> >> >> Range("K" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C)"
>> >> >> Range("L1").Select
>> >> >> Range("L" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C1)"
>> >> >> Range("R1").Select
>> >> >> Range("R" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C)"
>> >> >> Range("S1").Select
>> >> >> Range("S" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C)"
>> >> >> Range("T1").Select
>> >> >> Range("T" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C)"
>> >> >> Range("U1").Select
>> >> >> Range("U" + CStr(RowCount + 2)).Select
>> >> >> ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(RowCount) &
>> >> >> "]C:R[-1]C)"
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>