CODEWITHSUNDEEP
vbaexcelrangeformula
user3242245
user3242245

Reputation: 81

Vba: Find a percent change using a dynamic range

I have three ranges of data: D14:F28, H14:J28 and L14:N28. I wrote a macro that appends a new year of data at row 14, which increases the ranges to: D14:F29, H14:J29 and L14:N29.

I'm trying to develop another macro that calculates the percent change for each column in each range and puts the percent change two cells down from the last cell with data in it (e.g. (D14-D29)/D29 in D31 ect.). The problem is that I need the formula to be dynamic, so that when the next year of data is appended to the table, the percent change for each column in each new range is put two cells down from the last cell with data in it (e.g. (D14-D30)/D30 in D32 ect.). The same thing will happen with next years data when the macro is used, and so on and so forth.

This is the code that I've come up with:

Sub Percent_change_Calculation()
iRow = 14
Cell_val = Cells(iRow, 4)

While Cell_val <> ""
    iRow = iRow + 1
    Cell_val = Cells(iRow, 4)
Wend
Cells(iRow + 1, 4) = "=(D14-iRow)/iRow"
End Sub

But it doesn't calculate the percent change and '#NAME?' is entered in the cell. On the bright side it is in the cell that i want it to be in.

Thanks for the help in advance,

Upvotes: 0

Views: 1411

Answers (1)

Tim Williams
Tim Williams

Reputation: 166655

Sub Percent_change_Calculation()

    With Cells(14, 4).End(xlDown)

        .Offset(2, 0).Formula = Replace("=(D14 -{a})/{a}", _
                               "{a}", .Address(False, False))
    End With

End Sub

Upvotes: 1

Related Questions