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
--
Regards,

OssieMac


"Joseph Atie" wrote:

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