supes927
04-20-2010, 04:52 PM
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")
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
This must have been done a million times. Can anyone help me?
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")
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
This must have been done a million times. Can anyone help me?