Macro to Populate Formula Returning Zero

[BPW]

There is a small project I have been working on and I have ran into a problem that has been quite vexing. In short, I'm attempting to populate a cell a row below the first blank row with a Subtotal formula. The below code works and populates the correct formula in the location desired but the result is zero which clearly isn't the case as the formula's range has many numbers (formatted as numbers.)

Any ideas? Let me know if you need more info.

PS: I've very much a beginner with VBA for Excel.

Sub BPM_SUBTOTAL()

Dim ws As Worksheet
Set ws = Sheets("Sku Data")

Dim TrendRow As Long
Dim LastRowCount As Integer


LastRowCount = ActiveSheet.UsedRange.Rows.Count

TrendRow = Range("A2").End(xlDown).Offset(2, 24).Select
With ActiveCell

    .NumberFormat = "General"
    .Formula = "=SUBTOTAL(9,Y3:Y" & LastRowCount & ")"
End With


End Sub

 

[DataBlake]

If it doesn't need to be a formula try

Sub tester()
Dim lr As Long, x As Long, y As Long

lr = ActiveSheet.UsedRange.Rows.Count


For y = 3 To lr
    x = x + Range("Y" & y).Value
Next y

Range("Y" & lr + 2).Value = x

End Sub

if it needs to be a formula try looping through your code using F8 and see what each value is by hovering over it or using the Watch Window (view > watch window)
just highlight what you want to see the value of (example: LastRowCount) and drag it to the watch window
I assume its the formula itself though, which works for me. You're POSITIVE the values in Y are numbers and not text?

 

[BPW]

Thanks DataBlake! The most bizarre thing happened. I used a new version of my report and ran the script and it worked as intended. Thank you for taking the time to help a "newbie" with his VBA issues.