PDA

View Full Version : Solved: put a range into a variable



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?

austenr
04-20-2010, 05:20 PM
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
04-20-2010, 05:40 PM
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
04-20-2010, 06:32 PM
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
04-20-2010, 07:05 PM
...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
04-20-2010, 07:09 PM
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. :thumb

ZVI
04-20-2010, 07:23 PM
Thanks for all your help ZVI. :thumb
You are welcome & welcome to VBAX!
Vladimir :beerchug: