Hi again, lovely Excel people 🙂
I'm trying to insert a formula into a spreadsheet, to subtotal the visible rows in the sheet when I filter for various results. I have a working subtotal in an existing sheet (applied manually) but I am now trying to repeat this for new sheets from VBA.
The length of the sheet is not constant, so I have set some variables to provide the required numbers for the rows (FinalRow = the last row of Data, SubRow is two rows below this to insert the "Results" label in the previous column, and the Subtotal in the active column, and SubOffset which is 1 greater then FinalRow, to provide the offset up to row two (immediately below the headings), with the offset at the bottom of the range being -2 (the equivalent of the FinalRow, 2 rows above the subtotal).
Annoying thing is that I had it working, then we had a power out and I lost it (bad timing!!) and can't for the life of me remember what I had. Every permutation that I try comes up with various compilation errors on that line of code.
The code I currently have is
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-SubOffset]C,R[-2]C)"
where the cell for the subtotal is made active, and the FinalRow, SubRow and SubOffset values are all showing correct . If I manually enter the value for SubOffset in place of the variable, then the line executes perfectly.
How can I use the variable to set the offset to row 2 please?
Many thanks,
Tony.
Hi Tony
Give this a try. Select any cell in the column and run the macro. The formula will be inserted 2 rows below the last cell and will sum starting from row 2.
Change to suit your needs.
Sub SKAddSubtotal()
'Need to select any cell in the column
Dim LastRow As Long
Dim OffsetRow As Long
Dim ColLetter As String
'Formula will be 2 rows below the LastRow
OffsetRow = 2
'Get the selected column's letter
ColLetter = Split(Cells(1, Selection.Column).Address, "$")(1)
'Get the last row
LastRow = Range(ColLetter & Rows.Count).End(xlUp).Row
'Insert the text Result one column to the left of the selected column
Range(ColLetter & LastRow + OffsetRow).Offset(, -1) = "Result"
'Insert the formula 2 rows below the LastRow
Range(ColLetter & (LastRow + OffsetRow)).Formula = "=SUBTOTAL(9," & ColLetter & "2:" & ColLetter & LastRow & ")"
End Sub
Once again, thank you very much, Sunny. Absolute genius, and a blessing to have around!
I used your Insert Formula row, with the ColLetter variable, and my own FinalRow and SubRow variables, and in it went 🙂
Other than that, I'm pretty much there now with the formatting of that sheet, although I'm now getting a few oddball results from your 'Countweeks' function ... when a week is simply put in as a date e.g. 2/11/16, it displays as 02-Nov, and the function as applied to each cell in the range displays it as 01-Nov, but if I drag and copy the cell above then it calculates it as 1 which is correct!
Something else for me to play with (or just ask timetabling to be consistent with their output LOL).
Once again, much appreciated 🙂
Tony.
OK - sorted the date/weeks problem 🙂 - Instead of applying the function to each cell in the range, put it in the first cell and autofill! Same with the calculation in the next column too 🙂
Now to wait for the data to start arriving hehe.
Thanks again,
Tony.
Hi Tony
Glad it helps. No codes are 100% perfect the first time. Definitely need some fine tuning.
Good luck.
Cheers
Sunny
