General discussion

Locked

Using VBA how do I graph non-adjacent column data ?

By mgia0055 ·
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

This conversation is currently closed to new comments.

0 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Back to Software Forum
0 total posts (Page 1 of 1)  

Related Discussions

Related Forums