I’m trying to generate a line graph using VBA that selects data from alternating columns. I’ve included a spreadsheet that contains data for staff allocation (Forecast and Actual) over time.
I’d like to be able to create a graph that plots just the forecast columns for the following rows:
“Total Staff”
“Total Allocation”
“Remaining availability”
Alternatively, I’d like to create a graph that plots a comparison of Forecast vs Actual rows:
“Total Staff”
“Total Allocation” and
“Remaining availability”.
I’m having difficulties with understanding how to set the correct range.
I’m using the following code but it’s throwing an error:
code:
Sub linegraph()
‘
‘ linegraph Macro
‘
Dim AltRng As Range
Dim LastCol As Variant
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet
Set Wks = ActiveSheet
LastCol = Wks.Cells.Find(“*”, , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
‘column 5 starting point for forecast data
For C = 5 To LastCol Step 2
Set Rng = Wks.Columns(C)
If AltRng Is Nothing Then Set AltRng = Rng
Set AltRng = Union(AltRng, Rng)
Next C
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.SetSourceData Source:=Sheets(“Sheet1”).Range(AltRng)
.Chart.ChartType = xlXYScatterLines
End With
End Sub
Here’s an example of the table:
8/6/2010 8/13/2010 8/20/2010 8/27/2010 9/3/2010
Staff Summary Forecast Actual Forecast Actual Forecast Actual Forecast Actual Forecast Actual
Total Staff 60 60 60 60 60 60 60 60 60 60
Total Allocation 40.00 40.00 40.00 50.00 50.00 50.00 50.00 50.00 50.00 50.00
Total Allocation % 66.67% 66.67% 66.67% 83.33% 83.33% 83.33% 83.33% 83.33% 83.33% 83.33%
Remaining Availability 20.00 20.00 20.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Remaining Availability % 33.33% 33.33% 33.33% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67% 16.67%
Total DesignStaff 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00 10.00
Total Allocation 7 7 7 7 7 7 7 7 7 7
Remaining Availability 3.00 3.00 3.00 3.00 3.00 3.00 3.00 3.00 3.00 3.00
