CODEWITHSUNDEEP
excelvba
Tim Wilkinson
Tim Wilkinson

Reputation: 3801

VBA subtotal column and print in same column

I am trying to run a =subtotal() function on an entire column and paste the value in the same column, but you can't run the function in the same column as im trying to total.

ActiveCell.Value = "Total No. of chargeable hours"
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = "=SUBTOTAL(9,G:G)"

As this returns an error, I have tried using a helper cell to cut and paste but get a debug error on the PasteSpecial = xlPasteValues line.

ActiveCell.Value = "Total No. of chargeable hours"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "=SUBTOTAL(9,G:G)"
ActiveCell.Cut
ActiveCell.Offset(0, 1).Select
ActiveCell.PasteSpecial = xlPasteValues

The number of rows changes each time the sheet in question is created, so can't use a specific cell range.

Has anyone got a working way to achieve this?

Thanks.

Upvotes: 0

Views: 1023

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Sub WriteSubtotal()

    With ActiveCell
        .Value = "Total No. of chargeable hours."
        .Offset(0, 1).Value = Application.WorksheetFunction.Subtotal(9, .Parent.Range("G1").EntireColumn)
    End With

End Sub

Generally, you don't need to select or activate cells. Just refer to the cell and manipulate the properties or call the methods. The above sub puts a label in the active cell then changes the Value property of the cell to the right.

Note that the second argument to SUBTOTAL uses the .Parent property. Inside a With block for the ActiveCell, the .Parent property will refer to the sheet.

Upvotes: 2

hnk
hnk

Reputation: 2214

You need to write a formula that returns an array, so you can declare your function as 

Function SubTotal(InputRange as Range) as Variant
    Dim Output() as Variant
    ' ReDim Output(.... to the correct rows, column size)

    ' Run your subtotal code here
    ' Output (Row, 1) = YourAnswer(Row)

    SubTotal = Output
End Function

VBA will not allow a function to modify the spreadsheet with copy paste type operations.

Upvotes: 0

Related Questions