Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Range Variables in Formulas (Excel 97)

    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").V alue, 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.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Range Variables in Formulas (Excel 97)

    At first glance:
    ...
    Set myChartSource = Range([a33], myendCell)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Range Variables in Formulas (Excel 97)

    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.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using Range Variables in Formulas (Excel 97)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Range Variables in Formulas (Excel 97)

    "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

  6. #6
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Range Variables in Formulas (Excel 97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •