[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Excel VBA: using variables in Range object

Is there a way in the attached code to replace the a1 style cell reference for the range object with variables?
Sub AddOneSum()
Dim n As Integer
Dim RanngeToAdd As Range
Dim RangeSum As Integer
Dim LowerRange As String
Dim UpperRange As String
n = 1
LowerRange = "e1"
UpperRange = "e22"
RangeSum = 0
Set RangeToAdd = ActiveSheet.Range("e1:e11")
For Each Cell In RangeToAdd
Cell.Value = n
n = n + 1
RangeSum = RangeSum + Cell.Value
Next
Range("g12").Value = RangeSum
End Sub

Open in new window

0
BBlu
Asked:
BBlu
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

you mean:
Set RangeToAdd = ActiveSheet.Range(LowerRange & ":" & UpperRange)
0
 
peakpeakCommented:
Dynamic range:
=OFFSET($A$1,0,0,COUNT($A:$A),1)
0
 
BBluAuthor Commented:
Perfect!  I tried something similar, but thought I had to include the quotes.  Thanks.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now