• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Date Range Charting VBA

S

SharmaS

New Member
Chandoo, I am a big fan and had a pressing vba charting question. I have a set of dates (x) axis and several y1,y2.
My y data set looks like Mar 2010 April 2010 March 2011 April 2011 so on and so forth until April 2020( future options data).
What I need to do is use vba to chart this data separately for same x axis but march data in one table and April data in another. So 2 tables (march(2001..2020). april (2001-2020) from the same main data....
Within mar data using combo box I shld be able to dynamically select if I wnt to see mar 2010, 11, 12 ..or 2020 data, dynamically select whichever year i would like to see.. same with april.
Further within the dates (X axis) I should be able to select dynamically as well. Like selecting today's date and a date from 3 years ago or a date from two days ago going back to 20 days ago... etc (dynamic date range) for both of these charts.
Basically, chart should only plot prices for selected date range for the march or April contract..
please help Chandoo. I need some help from you but I do need to do this using vba only since my boss only wants vba. Pls reply friend. Thanks!
 
SharmaS

Firstly, Welcome to the Chandoo.org Forums

Can I please suggest that you post a sample file with your data so that we can assist you more specifically.

Also why do you want to use VBA? This can all be done without using VBA
 
Sure. Please find the file attached.

I need VBA cause my boss wants all of this done in VBA, I already did all of this in excel using dynamic named ranges but he is not impressed. Just started a new job at a hedge fund. Thank you so so so much Hui. I am right here waiting to hear from you. I am based in North America and it is evening where I am. so please feel free to let me know what you think. Thank you sir.
 

Attachments

  • HJJTestFile.xlsm
    313 KB · Views: 11
Hui said:
SharmaS

Firstly, Welcome to the Chandoo.org Forums

Can I please suggest that you post a sample file with your data so that we can assist you more specifically.

Also why do you want to use VBA? This can all be done without using VBA
Click to expand...
Sir, i posted the file. Thanks for your reply..Still new so figuring out the forum. Thanks!
 
Sharma

I would not even think of doing this in VBA
The main reasons are that it can be done in Excel quite easily and secondly it will be more robust in terms of future proof and ease of maintenance in Excel than VBA
As well an Excel solution will be much faster than a VBA solution

If you have an Excel solution please pass these comments to your boss
 
Last edited:
Hui said:
Sharma

I would not even think of doing this in VBA
The main reasons are that it can be done in Excel quite easily and secondly it will be more robust in terms of future proof and ease of maintenance in Excel than VBA

If you have an Excel solution please pass these comments to your boss
Click to expand...
I already gave him my excel solution and he says no i want it in VBA. Never mind Hui, thanks for offering to help though..:)
 
It's not hard to fill a range and name it for a chart in VBA. Hui's right though, a lot of times this stuff will last longer if you do it with formulas.

I still don't understand what you want the output to look like though. Can you update your file with a sample of the chart?
 
dan_l said:
It's not hard to fill a range and name it for a chart in VBA. Hui's right though, a lot of times this stuff will last longer if you do it with formulas.

I still don't understand what you want the output to look like though. Can you update your file with a sample of the chart?
Click to expand...
Dan, thank you so much for your reply. I have attached a file with charts in it for you.

Basically, I need two things (Both with the help of VBA only, thanks Boss!).

Within my charts, on the right hand side when i click on the years check boxes, those years should automatically populate on the charts, right now they are range bound so if I add say 2005 for eg, it messes up the chart.

2. You see # of daysto go and Time to go options on column O, basically, if i were to select a specific range, only those days should be populated on the charts. Does that make sense?

Please let me know!
 

Attachments

  • Copy of HJJTestFile.xlsm
    383.1 KB · Views: 12
dan_l said:
what's this "number of days to see" stuff all about?
Click to expand...
Basically be able to pick two X axis variables and then the Y axes data points would only be charted within those two ranges... so eg.. If i pick 40 for Time to go (which i should better name as lower data point in the range) and 250 for upper range (which is currently called # of days to see)..i should be able to chart data for only 40-250 X axis.
Dan, more importantly, I had one question which I think if i figure out, I should be able to come very close to what I need to do. My data set changes all the time, its dynamic. A user should be able to pick and chose from what ever they want to see both from x as well as y1,y2 axes.

if they pick they want to see values from 0-230 days only for say MarApril2005, MarApr2008 data points ONLY, then they should be able to do that. I have been trying to set up a named range which will be able to dynamically set my range using offset and index functions. But if you see in my sheet HJJMAData (march april data), there are formulas which when there is nothing to populate give "" which means an empty cell. However these are stil formulas, so basically, when i try the index or offset function to set a dynamic range, it picks all the values including the empty cells which contain no value but still have a formula. How can i fix that? so the dynamic named range picks only values...I unfortunately cannot get rid ofthe formulas since that is how they are dynamically populated by the user. Please and thank you so so so much! If you were here, i would buy you coffee!:)

I am here trying to figure this baby out.
 
sorry buddy, I don't really understand what we're trying to do here. But making a chart or adjusting some ranges with vba isn't that hard....
 
Looking interesting... :)

Lil bit fuzzy, with few assumption...
Please try..

Code: 
Sub OnlyVBA()
Application.ScreenUpdating = False
Sheets("Chart").Cells.Clear
With Sheets("RawData")
.Range("B1:ac1").EntireColumn.Hidden = True
  For I = 2 To 17
  If Sheets("FrontSheet").Range("V" & I) Then
  For j = 2 To 29
  If InStr(.Cells(1, j), Sheets("FrontSheet").Range("V" & I)) Then _
  .Cells(1, j).EntireColumn.Hidden = False
  Next j
  End If
  Next I
.Range("A2:a301").EntireRow.Hidden = True
On Error Resume Next
  .Range("A" & Application.Match(Sheets("FrontSheet").[j3].Value, .Range("A:A"), False)).Resize(Sheets("FrontSheet").[j5]).EntireRow.Hidden = False
On Error GoTo 0
  With .Range("A1").CurrentRegion
  .SpecialCells(12).Copy Sheets("Chart").Range("A1")
  .Cells.EntireColumn.Hidden = False
  .Cells.EntireRow.Hidden = False
  End With
End With

  With Sheets("Chart")
  .[a1].Clear
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=.Range("A1").CurrentRegion.Offset(, 1).Rows(1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
  .SetRange Sheets("Chart").Range("A1").CurrentRegion.Offset(, 1)
  .Orientation = xlLeftToRight
'  .SortMethod = xlPinYin
  .Apply
  End With
  .Columns(1).Copy
  .Range("a1").Offset(, (.Range("A1").CurrentRegion.Columns.Count + 1) / 2).Insert
  Application.CutCopyMode = False
  deb = (.Range("A1").CurrentRegion.Columns.Count / 2) + 1
  .Cells(1, deb).EntireColumn.Insert
  End With
   
  With Sheets("FrontSheet")
  .ChartObjects("AprChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, 1).CurrentRegion
  .ChartObjects("MarChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, deb + 1).CurrentRegion
  End With
End Sub
 

Attachments

  • DynamicChart SharmaS OnlyVBA.xlsm
    115.5 KB · Views: 21
Thanks Debraj. This is exactly what I needed. However, my boss is not too happy copying pasting/ duplicating raw data (I am having a hard time wrapping my head around the fact how could i plot this dynamic range without having another set of data to be able to do the needed manipulation. I showed him something similar yesterday but he says i should not use a second set of raw data. But what you did is great! Thanks friend.
 
Debraj said:
Looking interesting... :)

Lil bit fuzzy, with few assumption...
Please try..

Code: 
Sub OnlyVBA()
Application.ScreenUpdating = False
Sheets("Chart").Cells.Clear
With Sheets("RawData")
.Range("B1:ac1").EntireColumn.Hidden = True
  For I = 2 To 17
  If Sheets("FrontSheet").Range("V" & I) Then
  For j = 2 To 29
  If InStr(.Cells(1, j), Sheets("FrontSheet").Range("V" & I)) Then _
  .Cells(1, j).EntireColumn.Hidden = False
  Next j
  End If
  Next I
.Range("A2:a301").EntireRow.Hidden = True
On Error Resume Next
  .Range("A" & Application.Match(Sheets("FrontSheet").[j3].Value, .Range("A:A"), False)).Resize(Sheets("FrontSheet").[j5]).EntireRow.Hidden = False
On Error GoTo 0
  With .Range("A1").CurrentRegion
  .SpecialCells(12).Copy Sheets("Chart").Range("A1")
  .Cells.EntireColumn.Hidden = False
  .Cells.EntireRow.Hidden = False
  End With
End With
 
  With Sheets("Chart")
  .[a1].Clear
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=.Range("A1").CurrentRegion.Offset(, 1).Rows(1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
  .SetRange Sheets("Chart").Range("A1").CurrentRegion.Offset(, 1)
  .Orientation = xlLeftToRight
'  .SortMethod = xlPinYin
  .Apply
  End With
  .Columns(1).Copy
  .Range("a1").Offset(, (.Range("A1").CurrentRegion.Columns.Count + 1) / 2).Insert
  Application.CutCopyMode = False
  deb = (.Range("A1").CurrentRegion.Columns.Count / 2) + 1
  .Cells(1, deb).EntireColumn.Insert
  End With
  
  With Sheets("FrontSheet")
  .ChartObjects("AprChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, 1).CurrentRegion
  .ChartObjects("MarChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, deb + 1).CurrentRegion
  End With
End Sub
Click to expand...


Debraj, I just ripped apart what you did and realized how your data set in the chart sheet was getting automatically plotted depending on the range and the years i clicked. MAGIC! Nailed it. Only now i will sit and figure out how to replicate it for the billion other data sets that I have. If i have questions, i will bug you please? Thanks man!!
 
Debraj said:
Looking interesting... :)

Lil bit fuzzy, with few assumption...
Please try..

Code: 
Sub OnlyVBA()
Application.ScreenUpdating = False
Sheets("Chart").Cells.Clear
With Sheets("RawData")
.Range("B1:ac1").EntireColumn.Hidden = True
  For I = 2 To 17
  If Sheets("FrontSheet").Range("V" & I) Then
  For j = 2 To 29
  If InStr(.Cells(1, j), Sheets("FrontSheet").Range("V" & I)) Then _
  .Cells(1, j).EntireColumn.Hidden = False
  Next j
  End If
  Next I
.Range("A2:a301").EntireRow.Hidden = True
On Error Resume Next
  .Range("A" & Application.Match(Sheets("FrontSheet").[j3].Value, .Range("A:A"), False)).Resize(Sheets("FrontSheet").[j5]).EntireRow.Hidden = False
On Error GoTo 0
  With .Range("A1").CurrentRegion
  .SpecialCells(12).Copy Sheets("Chart").Range("A1")
  .Cells.EntireColumn.Hidden = False
  .Cells.EntireRow.Hidden = False
  End With
End With
 
  With Sheets("Chart")
  .[a1].Clear
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=.Range("A1").CurrentRegion.Offset(, 1).Rows(1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
  .SetRange Sheets("Chart").Range("A1").CurrentRegion.Offset(, 1)
  .Orientation = xlLeftToRight
'  .SortMethod = xlPinYin
  .Apply
  End With
  .Columns(1).Copy
  .Range("a1").Offset(, (.Range("A1").CurrentRegion.Columns.Count + 1) / 2).Insert
  Application.CutCopyMode = False
  deb = (.Range("A1").CurrentRegion.Columns.Count / 2) + 1
  .Cells(1, deb).EntireColumn.Insert
  End With
  
  With Sheets("FrontSheet")
  .ChartObjects("AprChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, 1).CurrentRegion
  .ChartObjects("MarChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, deb + 1).CurrentRegion
  End With
End Sub
Click to expand...
Hi, i could never figure out the whole code. It did not make sense to me. I am such a newbie at VBA. everything I tried gave me errors. Will you please break down the code for me a little. Thanks so much.
 
Hey Dev
Debraj said:
Looking interesting... :)

Lil bit fuzzy, with few assumption...
Please try..

Code: 
Sub OnlyVBA()
Application.ScreenUpdating = False
Sheets("Chart").Cells.Clear
With Sheets("RawData")
.Range("B1:ac1").EntireColumn.Hidden = True
  For I = 2 To 17
  If Sheets("FrontSheet").Range("V" & I) Then
  For j = 2 To 29
  If InStr(.Cells(1, j), Sheets("FrontSheet").Range("V" & I)) Then _
  .Cells(1, j).EntireColumn.Hidden = False
  Next j
  End If
  Next I
.Range("A2:a301").EntireRow.Hidden = True
On Error Resume Next
  .Range("A" & Application.Match(Sheets("FrontSheet").[j3].Value, .Range("A:A"), False)).Resize(Sheets("FrontSheet").[j5]).EntireRow.Hidden = False
On Error GoTo 0
  With .Range("A1").CurrentRegion
  .SpecialCells(12).Copy Sheets("Chart").Range("A1")
  .Cells.EntireColumn.Hidden = False
  .Cells.EntireRow.Hidden = False
  End With
End With
 
  With Sheets("Chart")
  .[a1].Clear
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=.Range("A1").CurrentRegion.Offset(, 1).Rows(1), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
  .SetRange Sheets("Chart").Range("A1").CurrentRegion.Offset(, 1)
  .Orientation = xlLeftToRight
'  .SortMethod = xlPinYin
  .Apply
  End With
  .Columns(1).Copy
  .Range("a1").Offset(, (.Range("A1").CurrentRegion.Columns.Count + 1) / 2).Insert
  Application.CutCopyMode = False
  deb = (.Range("A1").CurrentRegion.Columns.Count / 2) + 1
  .Cells(1, deb).EntireColumn.Insert
  End With
  
  With Sheets("FrontSheet")
  .ChartObjects("AprChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, 1).CurrentRegion
  .ChartObjects("MarChrt").Activate
  ActiveChart.SetSourceData _
  Source:=Sheets("Chart").Cells(1, deb + 1).CurrentRegion
  End With
End Sub
Click to expand...

Hello. Could you please take a look at the following code and advise on why it isnt working. This one is way superior to how you had written your code. I am trying to understand instead of simply copying what you gave me.
Sub OnlyVBA()
Dim Start, Finish As Integer
' Getting the starting and ending ranges (rows from rawdata sheet)
If (IsNumeric(Sheets("FrontSheet").[J3].Value) And Sheets("FrontSheet").[J5].Value) Then
Start = Sheets("FrontSheet").[J5].Value
Finish = Sheets("FrontSheet").[J3].Value
End If
Dim YArray1(1000) As Double
Dim YArray2(1000) As Double
ReDim XArray(Finish - Start + 10) As Integer
For i = Start To Finish
For j = 1 To 16
If (IsNumeric(Sheets("FrontSheet").Range("V" & i))) Then

YArray1(j) = Sheets("Rawdata").Cells(i, j * 2).Value
YArray2(j) = Sheets("Rawdata").Cells(i, (j * 2) + 1).Value
End If
Next
Next
For x = Start To Finish
XArray(x - Start + 1) = x
Next
With Sheets("FrontSheet")
' Populating first chart
.ChartObjects("AprChrt").Activate
ActiveChart.SeriesCollection.Item(1).Values = YArray1
ActiveChart.SeriesCollection.Item(1).XValues = XArray

'Populating second chart
.ChartObjects("MarChrt").Activate
ActiveChart.SeriesCollection.Item(1).Values = YArray2
ActiveChart.SeriesCollection.Item(1).XValues = XArray

End With
End Sub
 
You must log in or register to reply here.
Back
Top