Excel VBA: using variables in Range object

Posted on 2009-12-19
Last Modified: 2012-05-08
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


Range("g12").Value = RangeSum

End Sub

Open in new window

Question by:BBlu
    LVL 142

    Accepted Solution


    you mean:
    Set RangeToAdd = ActiveSheet.Range(LowerRange & ":" & UpperRange)
    LVL 28

    Expert Comment

    Dynamic range:

    Author Closing Comment

    Perfect!  I tried something similar, but thought I had to include the quotes.  Thanks.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now