CODEWITHSUNDEEP
excelvba
Runeaway3
Runeaway3

Reputation: 1449

Referring to a range based on variables in Excel?

I am trying to set a cell to a sum of a few cells.

The cells that will be summed are variable, and based on other things (and will change on each iteration of the macro).

I coded such that I will know the row that the cells are on, as well as the column of the beginning cell and the column of the ending cell (ie. perhaps A600 to A606 will be summed). However, I have set these column and row assignments to variables, say Y, Xstart, and Xend. How do I have the sum select these ranges, of the cells which are purely defined by variables?

This was my initial guess.

set rng = "=SUM(& Y & Xstart &, & Y & Xend &)"

Upvotes: 0

Views: 548

Answers (1)

user6432984
user6432984

Reputation:

Use set when setting a reference to an object. Range.Formaula take a string argument and so does "=Sum()". 

Sub Example()

    Dim Xstart As Long, Xend As Long
    Dim TargetRange As Range, SumRange As Range
    Dim Y as string
    Y = "B"

    Xstart = 2
    Xend = 100

   Set SumRange = Range(" & Y &  & Xstart & ":" & Y & Xend)
   Set TargetRange = Range("A1")

   TargetRange.Formula = "=SUM(" & SumRange.Address & ")"

End Sub

Upvotes: 3

Related Questions