Using Range Variables in Formulas (Excel 97)

Topic

New Reply

I am attempting to use a range variable to define the variable end point in a chart source and am confused by the debug messages I am getting. Here’s what I have so far:
Dim myEndCell As Range
Dim myChartSource As Range
Rows(“26:26”).Select
Selection.Find(What:=Sheets(“pivot”).Range(“h2”).Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
Set myEndCell = ActiveCell.Offset(9, 0)
MsgBox myEndCell.Address
Set myChartSource = (“a33:&myendCell”) – My problem starts here
MsgBox myChartSource
Sheets(“TREND CHART G”).Select
ActiveSheet.ChartObjects(“Chart 3”).Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(“GAME CHART SOURCE”).Range(myChartSource), PlotBy:=xlRows

Any solutions and advice on how to use range variables would be much appreciated.

Reply | Quote

Replies

At first glance:
…
Set myChartSource = Range([a33], myendCell)

Reply | Quote

John, thank you; your first glance was enough. However, why does the a33 have to be enclosed in [ ] instead of ” “and why is no & used in front of the variable name? Please can you point me towards some source so that I can learn more about this? Mike.

Reply | Quote

Mike, there are two Range syntaxes:

Range(“A33”) and Range(“A33:R33”) are valid syntax which return a single or multiple cell Range

Range(Cell1, Cell2) and Range(Range1, Range2) are valid syntax which return a multiple cell Range (or a single cell if the two arguments refer to the same cell) in which the two arguments anchor the upper left and lower corners respectively.

Using Range(“A33”) is valid syntax, but
Range(“A33”, myEndCell) is not, as “A33” is a string, not a Cell or a Range, so since you already had myEndCell as a valid range for Cell2, it is

Range(Range(“A33”), myEndCell)

… and [A33] is a shorthand way of saying Range(“A33”).

(It could also have been Range(Cells(1,33), myEndCell), but that’s getting unnecessarily arcane.)

I hope I haven’t confused you. I just use the Help … A LOT!

Reply | Quote

“a33” in VBA is just a string and does not repesent a range as such. What you are trying to achieve is build a range which starts with one cell (a range) and ends with another cell (another range) so the correct VBA is

Set myChartSource = Range(Range(“A33”), myendCell)

John used the Evaluation operator ([ ]) as a sort of shorthand for Range(“A33”)

You could also have used

Set myChartSource = Range(“a33:” & myendCell.Address)

or

Set myChartSource = Range(Range(“A33”), ActiveCell.Offset(9, 0)), which is the same as Range([A33],ActiveCell.Offset(9, 0))

Andrew C

Reply | Quote

John and Andrew, thank you both very much for your explanations. I have learned a lot of very good stuff from this post.

Reply | Quote