| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cleaner coding to repeat process
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177668 Microsoft Office Help |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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" wrote in message ... 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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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" wrote in message ... 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" wrote in message ... 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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
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" wrote in message ... 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" wrote in message ... 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 . . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cleaner coding to repeat process
Ryan,
Again thanks, have adapted to suit my needs and learnt much from your experience. Ta, Rob "Ryan H" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code cleaner | Excel Discussion (Misc queries) | |||
| What is a VBA Cleaner and why using it? | Excel Programming | |||
| How to count process running time ( process not finished) | Excel Programming | |||
| How to count process running time ( process not finished) | Excel Programming | |||
| VB Code Cleaner | Excel Programming | |||
Linear Mode
