setting the source data for a chart in VBA

[Joseph Atie]

Need a little help, ive got the chart part down, the problem im having is
trying to define the range

the function below is passed an integer. the rows i need to use will be 5
rows below the "row" value. if i use rows(58:60) vba is happy. problem is the
last row is variable and the values will be 5 rows below the last row. how
can i express this???

this is the function im having trouble with

also im having trouble getting the title to work so any help with that would
also be appreciated

Sub chart_build(row As Integer)

Sheets("Labour Histogram").Select
ActiveChart.SetSourceData Source:=Sheets("Calc").Rows((row+5)row+7)),
PlotBy:= _
xlRows

ActiveChart.ChartTitle.text = Sheets("interface").Range("B5")

End Sub

note this is the functions thta builds the chart data incase you wanna know
where im coming from

Sub total(column As Integer, row As Integer)
Dim counterr As Integer
Dim counterc As Integer
Dim total As Integer

For counterc = 1 To (column * 2)
total = 0
For counterr = 1 To row
total = total + Cells(counterr + 2, counterc + 1).Value
If counterr = row Then
Cells(counterr + 3, counterc + 1).Value = total
End If
Next counterr
Next counterc
counterr = 1
counterc = 1
For counterc = 1 To (column * 2)
If counterc = 1 Then
Cells(row + 6, 1).Value = "Day Shift"
Cells(row + 7, 1).Value = "Night Shift"
End If
'date
Cells(row + 5, counterr + 1).Value = Cells(1, counterc + 1).Value
'day shift
Cells(row + 6, counterr + 1).Value = Cells(row + 3, counterc +
1).Value

counterc = counterc + 1
'night shift
Cells(row + 7, counterr + 1).Value = Cells(row + 3, counterc +
1).Value
counterr = counterr + 1
Next counterc
End Sub

 

[OssieMac]

Hi Joseph,

I am assuming that the chart is already set up and it is just the range you
want to change and also set the title. Hope you can follow the following
example.

Basically you cannot use VBA variables in the chart because they can loose
their value when the code is finished. Using named cells/ranges is the same
as naming a cell in the worksheet interactive mode and the name remains with
the worksheet and is saved with it. The name can then be used in lieu of the
actual cell address.

Feel free to get back to me if you have any problems with it.

Sub AssignChartRange()

Dim lastCell 'As Range

'Find the last cell of the range series range.
'Set lastCell will not work if Chart is selected.
'Therefore must select the worksheet first.
Sheets("Calc").Select
With Sheets("Calc")
'Edit AA to the last column of your data
Set lastCell = .Cells(.Rows.Count, "AA").End(xlUp)
End With

'Assign a Name to the last cell in the range
ActiveWorkbook.Names.Add Name:="EndOfRnge", _
RefersToR1C1:=lastCell

With Worksheets("Labour Histogram") _
.ChartObjects("Chart 1").Chart

.SetSourceData Source:=Sheets("Calc") _
.Range("A5:EndOfRnge"), PlotBy:=xlRows

.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"

'Following added for information.
'Delete if not required
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle. _
Characters.Text = "My X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle. _
Characters.Text = "My Y Axis"
End With

End Sub

 

[Joseph Atie]

..SetSourceData Source:=Sheets("Calc") _
..Range("A5:EndOfRnge"), PlotBy:=xlRows

does that above piece mean it will chart everything from a5 to the last used
cell on the page?

i only want to chart 3 rows the problem is the rows will always be last 3
rows on the sheet

please note the number of columns and rows is a variable in the script,
sometimes it actually exceeds the max columns(and we need to do it in 2 or
more sections) other times it only 20 or 30 columns same with the rows. it
all depends on the date range and no of tasks that need to be processed.

why does it work if i use for example row(58:60) but noy when i use a
variable? is there a way of using a text version of the variable to give the
same meaning


and i actually want to use cell on another sheet as my title

the sheet name is interface and the cell is b5 (again for example), is this
possible?

my understanding of vba isnt great as you can probably tell from my coding
style.

 

[OssieMac]

Hi Joseph,

Your quote "I only want to chart 3 rows the problem is the rows will always
be last 3 rows on the sheet"

Perhaps I interpreted incorrectly. Anyway try the following. It selects the
last 3 entire rows and names them as the range for the chart series. However,
it assumes that the column headers are on the first row of the 3 rows.

Your quote: "sometimes it actually exceeds the max columns (and we need to
do it in 2 or more sections)" I have not made any attempt to handle multiple
charts if the data exceeds specific limits. You will need a method of
counting the rows and columns and then treat each section as a separate
entity. I have included a line of code to show you how to find the last
column and from there you can decide whether to divide the data into several
charts. Having decided on the size of your charts, you can name the first and
last cell of the ranges for chart 1, chart 2 etc.

If you are uncertain of the name of an existing chart to include it in your
code like where I have used Chart 1, you can select any cell in a worksheet
and then turn on the macro recorder and then select the chart and turn the
macro recorder off and you will be able to find the name in the recorded code.

Sub AssignChartRange()

Dim lastRowNumb As Long
Dim firstRowNumb As Long
Dim lastColNumb As Long

'Find the last used row on the worksheet and
'calculate the first row for chart series range.
'If chart is selected then code below fails.
'Therefore select the worksheet first.
Sheets("Calc").Select
With Sheets("Calc")
'Edit "A" to any column of your data where there
'will be data in the last row.
lastRowNumb = .Cells(.Rows.Count, "A").End(xlUp).Row
firstRowNumb = lastRowNumb - 2

'Following is example only for finding last used
'column number on the last used row.
'Not used for anything in the remaining code
lastColNumb = .Cells(lastRowNumb, .Columns.Count) _
.End(xlToLeft).Column
End With

'Name the 3 rows for the chart series range
ActiveWorkbook.Names.Add Name:="ChartSeriesRnge", _
RefersToR1C1:=Sheets("Calc").Range(Cells(firstRowNumb, "A"), _
Cells(lastRowNumb, "A")).EntireRow

With Worksheets("Labour Histogram") _
.ChartObjects("Chart 1").Chart

.SetSourceData Source:=Sheets("Calc") _
.Range("ChartSeriesRnge"), PlotBy:=xlRows

.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"
.ChartTitle.Font.Bold = True

'Following added for information.
'Delete if not required
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle. _
Characters.Text = "My X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle. _
Characters.Text = "My Y Axis"
End With

End Sub

 

[Joseph Atie]

thanks very much for your help

ill give it a try when i get some time.

 

[Jon Peltier]

You don't need VBA at all, just a dynamic range definition in the worksheet.
This is similar to the "last 12 months" example:

Overview of dynamic charts:
http://peltiertech.com/Excel/Charts/Dynamics.html

Specific example:
http://peltiertech.com/Excel/Charts/DynamicLast12.html

- Jon