Cleaner coding to repeat process

R

Rob

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
 
R

Ryan H

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
 
J

joel

Sub Subtotal()
Dim RowCount As Long

Mycolumns = Array("K","L","R","S","T","U")


LastRow = Range("K" & rows.count).end(xlup).row
AddRow = LastRow + 2
for each Col in Mycolumns
Range(Col & AddRow).FormulaR1C1 = "=SUBTOTAL(9,R[-" + CStr(AddRow)
"]C:R[-1]C)"

next Col

End Su
 
R

Rob

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


.
Click to expand...
Click to expand...
 
R

Ryan H

What range are you wanting to sum? You may not need to use R1C1 notation
style.
--
Cheers,
Ryan


Rob said:
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 said:
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 said:
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


.
Click to expand...
Click to expand...


.
Click to expand...
 
R

Rob

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 said:
What range are you wanting to sum? You may not need to use R1C1 notation
style.
--
Cheers,
Ryan


Rob said:
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 said:
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


:

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


.
Click to expand...


.
Click to expand...
Click to expand...
 
R

Ryan H

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 said:
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 said:
What range are you wanting to sum? You may not need to use R1C1 notation
style.
--
Cheers,
Ryan


Rob said:
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

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


:

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


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
R

Rob

Ryan,

Again thanks, have adapted to suit my needs and learnt much from your
experience.

Ta, Rob
Ryan H said:
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 said:
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 said:
What range are you wanting to sum? You may not need to use R1C1
notation
style.
--
Cheers,
Ryan


:

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

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


:

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


.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AutoFilter and Sub-total 6
how to: c.value = "a cell value" 5
Efficiency of Code Help 4
VBA Code and Summing Next Non-Blank Cell in Column 9
Sum Variable Range of Cells 6
Create Detail Sheets from Pivot Table 1
Copying Worksheets in Different Books to One Workbook 2
Totals An Easier Way?? 5

Top