title: Using VBA to select a dynamic range of cells and create a chart
body: <p>I am attempting to use VBA to create a chart using dynamic ranges. Specifically, I have an <code>Excel table</code> as follows</p>
  
  <p><img src="https://i.stack.imgur.com/8OaOK.png" alt="Data Table"></p>
  
  <p>Based on this data, I would like to create a chart, with the date ranges changed as per requirement. For example, at one instance, I would be required to produce a chart for 1st July - 6th July, and at another, from 10th July - 14th July.</p>
  
  <p><strong><em>The following is my attempt at generating such a chart, but I feel there would be much better ways other than mine. Hence, my question, is there any other, better way?</em></strong></p>
  
  <p>1- I first enter the date values in 'helper cells' for which a chart is sought. In this case, cell M24 has the value 10th July, while cell M26 has the value 14th July.</p>
  
  <p>2- Then, I use the <code>match()</code> function to find the positions from the date column of my Table. The function is <code>=MATCH(M24,Table1[Dates],0)</code>, and <code>=MATCH(M26,Table1[Dates],0)</code>.</p>
  
  <p>3- Given that I have the relative positions for the dates, I then use the following VBA code to generate the chart:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
      Dim mySheet As Worksheet
      Dim myShape As Shape
      Dim myChart As Chart
      Dim myVal1 As String
      Dim myVal2 As String
  
      Set mySheet = ActiveWorkbook.Worksheets("dataSheet")
      If myShape Is Nothing Then
          Set myShape = mySheet.Shapes.AddChart(XlChartType:=xlColumnClustered, _
              Left:=CommandButton1.Left + CommandButton1.Width + 2, _
              Width:=370, Height:=200)
      End If
  
      'In the following, I am offsetting from the first cell
      'of my Table, which contains the `value 1-Jul.
      'My objective is to use the range 10-Jul to 14th Jul,
      'so I also add a column offset
      'Cells O24 and O26 contain the results of the match functions
  
      myVal1 = Range("B4").Offset(Range("O24").Value, 0).Address
      myVal2 = Range("B4").Offset(Range("O26").Value, 4).Address
      Set myChart = myShape.Chart
      myChart.ChartType = xlLine
      myChart.SetSourceData Source:=Sheets("dataSheet") _
          .Range(CStr(myVal1 &amp; ":" &amp; myVal2))
  End Sub
  </code></pre>
  
  <p>So, now hoping that my question is clear, could somebody please educate me of a better method than this one? This seems to be more a hacking method than proper coding to me...</p>
  
  <p>Many thanks in advance!</p>