Macro to Populate Formula Returning Zero

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
If it doesn't need to be a formula try
VBA Code:
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?
 
  • Like
Reactions: BPW

BPW

New Member
Joined
Mar 7, 2013
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,793
Messages
5,638,360
Members
417,023
Latest member
Zimbo38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top