Dynamic Range In A Formula VBA

Question

I am trying to find the SUM of the last 5 rows.

This is my code:

Sub SumRange()

Dim upperrang As Long
Dim lowerring As Long
Dim testi As Long

lowerring = Range("A1").End(xlDown).Row
upperrang = Range("A1").End(xlDown).Row - 5

testi = "=SUM(C& upperrang:C& lowerring)"
Range("D20").Value = testi

End Sub

The error i'm getting is "type mismatch"

Thanks for your help!

https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — BigBen, Jun 16 '20 at 20:52
`Range("ActiveCell")` should just be `ActiveCell`... except, don't use `ActiveCell`, and don't use `Select` either.... more reading [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jun 16 '20 at 20:52

score 0 · Answer 1 · answered Jun 17 '20 at 01:37

0

To sum the last 5 entries:


Sub Test()

  Dim RngCnt As Long
  Dim I      As Long
  Dim Total  As Long

  RngCnt = Range("List").Count  #List is your Dynamic Range Name
  Total = 0

  For I = RngCnt To RngCnt - 4 Step -1
     Total = Total + Range("List").Cells(I, 1).Value
  Next I

End Sub

HTH

answered Jun 17 '20 at 01:37

RetiredGeek

2,980
1
7
21

Sub SumRange() Dim upperrang As Long Dim lowerring As Long Dim testi As Long lowerring = Range("A1").End(xlDown).Row upperrang = Range("A1").End(xlDown).Row - 10 testi = "=SUM(C& upperrang:C& lowerring)" Range("D20").Value = testi End Sub – OnTheHunt Jun 18 '20 at 00:29

Dynamic Range In A Formula VBA

1 Answers1