Using a variable in a refernce to chart data

[taltyr]

I have code that finds the columns of the earliest and latest dates for a Cashflow. I want to use these as part of a range specification for a Chart.

This is what I have so far;

Dim StartCol As Integer
Dim FinishCol As Integer
Dim MinDate As Long
Dim GraphStartCol As Integer
Dim MaxDate As Long
Dim GraphFinishCol As Integer
Dim AllStartDates As Range
Dim AllFinishDates As Range

' Set up Graph
' Get Earliest Date
MinDate = Application.WorksheetFunction.Min(Range("AllStartDates"))
' Get Graph Start Column
GraphStartCol = (Application.Match(CLng(MinDate), Range("1:1"), 1)) - 2


' Get Latest Date
MaxDate = Application.WorksheetFunction.Max(Range("AllFinishDates"))
' Get Graph Finish Column
GraphFinishCol = (Application.Match(CLng(MaxDate), Range("1:1"), 1)) + 2
'MsgBox (GraphStartCol & " " & GraphFinishCol)

' Set Graph Data
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Sheets("Cashflow").Range("A1,H1:CM1,A25:A26,H25:CM26")

What I need to do is change the refernce to H1:CM1 Etc. so that the ranges use the GraphStartCol and GraphFinishCol results.

Thank you for your help.

 

[p45cal]

Not the way I'd do it but this keeps the changes to a minimum; try:

Dim StartCol As [COLOR=Red]Long[/COLOR]
Dim FinishCol As [COLOR=Red]Long[/COLOR]
Dim MinDate As Long
Dim GraphStartCol As [COLOR=Red]Long[/COLOR]
Dim MaxDate As Long
Dim GraphFinishCol As [COLOR=Red]Long[/COLOR]
Dim AllStartDates As Range
Dim AllFinishDates As Range
[COLOR=Red]Dim GraphStartColLetters As String
Dim GraphFinishColLetters As String[/COLOR]
' Set up Graph
' Get Earliest Date
MinDate = Application.WorksheetFunction.Min(Range("AllStartDates"))
' Get Graph Start Column
GraphStartCol = (Application.Match(CLng(MinDate), Range("1:1"), 1)) - 2
[COLOR=Red]GraphStartColLetters = Split(Cells(1, GraphStartCol).Address, "$")(1)[/COLOR]

' Get Latest Date
MaxDate = Application.WorksheetFunction.Max(Range("AllFinishDates"))
' Get Graph Finish Column
GraphFinishCol = (Application.Match(CLng(MaxDate), Range("1:1"), 1)) + 2
[COLOR=Red]GraphFinishColLetters = Split(Cells(1, GraphFinishCol).Address, "$")(1)[/COLOR]

'MsgBox (GraphStartCol & " " & GraphFinishCol)

' Set Graph Data
ActiveSheet.ChartObjects("Chart 2").Activate
[COLOR=Red]ActiveChart.SetSourceData Source:=Sheets("Cashflow").Range("A1," & GraphStartColLetters & "1:" & GraphFinishColLetters & "1,A25:A26," & GraphStartColLetters & "25:" & GraphFinishColLetters & "26")[/COLOR]

 

[taltyr]

Thanks p45cal that did the trick. Also I know what you mean by "Not the way I'd do it", this piece forms part of my first real go at VBA. It does do what I want but I am sure that, if I keep learning VBA, I will look back on this one and see a much a better way of doing it.

Thanks anyway, now I must see how that SPLIT Function works.