title: VBS for excel: Using a script variable in Range selection
body: <p>I've seen similar questions asked for using VBA in excel, but I'm using VBScript so it's a bit different. The answers to the other questions gave me some clues, but I just don't know how to fix this.</p>
  
  <p>I'm using .SetSourceData to define a range to use in a graph. It works fine to define the range from the first to the last row (whole sheet) but I want to define a dynamic range using a variable defined earlier. So this works:</p>
  
  <pre><code>.SetSourceData objWorksheet.Range("E1","F" &amp; LastRow), xlColumns
  </code></pre>
  
  <p>where LastRow is defined as <code>LastRow=objWorksheet.UsedRange.Rows.Count</code>. Now what I want to do is use a range starting from the last row, going back a few rows. So my thought was to do this:</p>
  
  <pre><code>.SetSourceData objWorksheet.Range(Rowstring,"F" &amp; LastRow), xlColumns
  </code></pre>
  
  <p>where RowString is defined as </p>
  
  <pre><code>WeekNumber = LastRow - 1000
  RowString = "E" &amp; WeekNumber
  </code></pre>
  
  <p>But it gives me an error. How can I use a string variable in a range, and set it as source data for chart generation? Here's the code I'm using</p>
  
  <pre><code>    WeekNumber = LastRow - 1000
      RowString = "E" &amp; WeekNumber
      WScript.echo(RowString)
  
  
  ' Data for last week
  
  Sub LastWeek()
      WeekNumber = LastRow - 1000
      RowNumber = LastRow - WeekNumber
      RowString = "E" &amp; RowNumber
  End Sub
  
  myrange = "E1:E52"
  
  Set Range1 = objWorksheet.Range(myrange)
  
  ' Define chart properties 
  
  Set objChart = objExcel.Charts.Add()
      With objChart
          'define chart type
          .ChartType = xlXYScatterLinesNoMarkers
          'format chart
          .SeriesCollection(1).Border.Color = RGB(255, 0, 0)
          REM .ChartArea.Color = RGB(255, 255, 255)
          .PlotArea.Interior.Color = RGB(255, 255, 255)
          .HasTitle = True 
          .ChartTitle.Text = "usage"
          .Axes(xlCategory, xlPrimary).HasTitle = True
          .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
          .Axes(xlValue, xlPrimary).HasTitle = True
          .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "test"
          .HasLegend = False
          'define data
          .SetSourceData objWorksheet.Range(RowString,"F" &amp; LastRow), xlColumns
          .Export "Test.png", "PNG"
      End With
  </code></pre>
  
  <p>Thank you in advance</p>