Solved: put a range into a variable

[supes927]

I thought this would be a relatively common situation but I can't find any examples anywhere.
My data is only 2 columns wide. I have a loop that moves down one column finding the largest value. What I also want to do is store the cell addresses (for the active cell and the other to its right) in a variable before it moves on to the next row.
When the loop is finished I'm looking to use the range variable in the ".SetSourceData Source:=" line when plotting the chart instead of hard coding a range, as the largest value could be anywhere.
This is the start I've made on the code. (The two lines I have no idea about are:
"Set rng = Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 1).Address)"
".SetSourceData Source:=Sheets("Sheet1").Range(rng), PlotBy:=xlRows")

[vba]
Sub AddNewChart()
Range("I9").Select
Dim chtChart As Chart
Dim bigNum As Integer
Dim rng As Range
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value > bigNum Then

bigNum = ActiveCell.Value

Set rng = Range(ActiveCell.Address & ":" & ActiveCell.Offset(0, 1).Address)

End If

ActiveCell.Offset(1, 0).Select
Loop
Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With chtChart
.ChartType = xlColumnClustered
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").rng, PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "Scores"
.HasLegend = False

With .Parent
.Top = Range("F9").Top
.Left = Range("F9").Left
.Name = "MyChart"
End With

End With
End Sub
[/vba]

This must have been done a million times. Can anyone help me?

[austenr]

What is the value of bigNum? I see it defined but I dont see where it is assigned a value unless I am missing something.

[ZVI]

Hi supes927,
Try:

Sub AddNewChart1()
  
  Dim Rng As Range, chtChart As Chart
  
  ' Set Rng to the row with maximum value
  With Range("I9", Range("I" & Rows.Count).End(xlUp))
    Set Rng = .Find(WorksheetFunction.Max(.Value), LookIn:=xlValues, LookAt:=xlWhole).Resize(, 2)
    Rng.Select  ' <-- it's just to show
  End With
  
  ' Try to delete the previously created chart
  On Error Resume Next
  ActiveSheet.ChartObjects("MyChart").Delete
  On Error GoTo 0
  
  ' Create new chart
  Set chtChart = Charts.Add
  Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
  With chtChart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Rng, PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Text = "Scores"
    .HasLegend = False
    With .Parent
      .Top = Range("F9").Top
      .Left = Range("F9").Left
      .Name = "MyChart"
    End With
  End With
  
End Sub

Vladimir
P.S. Warning: the code has been fixed up a bit

[supes927]

Thanks very much to austenr and ZVI for replying.

(Quick note for austenr - bigNum starts at 0 as it enters the loop. I think VB does this by default when you don't define it. A lot of coders will probably tell me that's a bit lazy but that's often what I do).

Your code is fantastic KVI. It does exactly what I was looking for.

As I beginner though I'm not familiar at all with a couple of aspects of your solution (specifically the .End(xlUp) and the .Resize). Do you happen to know if Microsoft or anyone else have a resource to look up methods/functions and their usage?

[ZVI]

...Do you happen to know if Microsoft or anyone else have a resource to look up methods/functions and their usage?

I would recommend John Walkenbach's "Excel 2003 Power Programming with VBA” or "Excel 2007 Power Programming with VBA”, VBA online help, and active analyzing the suggestions of Excel forums like VBAX or MrExcel. Trial & error method is good as well

As to the End and Resize properties - VBA help says enough about it with providing of the code examples.
To see interactive VBA help in the code place the cursor into any part of the mentioned above properties and press Ctrl-F1.

[supes927]

I would recommend John Walkenbach's "Excel 2003 Power Programming with VBA” or "Excel 2007 Power Programming with VBA”, VBA online help, and active analyzing the suggestions of Excel forums like VBAX or MrExcel. Trial & error method is good as well

Thanks for all your help ZVI.

[ZVI]

Thanks for all your help ZVI.

You are welcome & welcome to VBAX!
Vladimir