Excel VBA- Dynamic formula returning zero value for range

Question

I am trying to have vba create a formula for two ranges within the same data set, as input into variables lastRow and lastRow2. However, when I try to create and calculate the formula, instead of getting the value of the last cell I get a zero. Offending code below:

Dim lastRow As Long
Dim lastRow2 As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    lastRow2 = Range("M" & Rows.Count).End(xlUp).Row


...



    'Calculate ATRT 2014 at cell B4
    Range("B6").Formula = "=(SUM(J11:J" & lastRow & ")/ SUM(I11:I" & lastRow & "))"
    Range("E6").Formula = "=(SUM(U11:U" & lastRow2 & ")/ SUM(T11:T" & lastRow2 & "))"
    Range("H6").Formula = "=E6-B6"

Running this gets two formulas: =(SUM(J11:J0)/ sum(I11:I0)) and =(SUM(U11:U0)/ SUM(T11:T0)). Why is the end of the range a zero???

Heh that's what I asked in your other question. Are the ranges all on the same sheet? — findwindow
Yes, the ranges are on the same sheet, in the same table. 1 table, but two different ranges — camelCaseCowboy
Hmm are you running the macro while the desired sheet is active? If so, then I am stumped. Unless you didn't really mean columns A/M... Edit: huh. Even if the column is empty, it should still be at least 1.... we need more code. — findwindow

Jason Brady Jason Brady · Accepted Answer · 2016-05-23T19:07:33

I'm pretty sure what is happening if you are not defining which worksheet the range() and row() objects are on so VBA is guessing and probably guessing incorrectly. Try adding a worksheet object and then defining all the ranges and rows to use be on that worksheet.

Dim lastRow As Long
Dim lastRow2 As Long
Dim ws As Worksheet

Set ws = ActiveSheet
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
lastRow2 = ws.Range("M" & ws.Rows.Count).End(xlUp).Row


'...



'Calculate ATRT 2014 at cell B4
ws.Range("B6").Formula = "=(SUM(J11:J" & lastRow & ")/ SUM(I11:I" & lastRow & "))"
ws.Range("E6").Formula = "=(SUM(U11:U" & lastRow2 & ")/ SUM(T11:T" & lastRow2 & "))"
ws.Range("H6").Formula = "=E6-B6"

Excel VBA- Dynamic formula returning zero value for range

1 Answers