VBS for excel: Using a script variable in Range selection

Question

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.

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:

.SetSourceData objWorksheet.Range("E1","F" & LastRow), xlColumns

where LastRow is defined as LastRow=objWorksheet.UsedRange.Rows.Count. 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:

.SetSourceData objWorksheet.Range(Rowstring,"F" & LastRow), xlColumns

where RowString is defined as

WeekNumber = LastRow - 1000
RowString = "E" & WeekNumber

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

    WeekNumber = LastRow - 1000
    RowString = "E" & WeekNumber
    WScript.echo(RowString)


' Data for last week

Sub LastWeek()
    WeekNumber = LastRow - 1000
    RowNumber = LastRow - WeekNumber
    RowString = "E" & 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" & LastRow), xlColumns
        .Export "Test.png", "PNG"
    End With

Thank you in advance

WeekNumber = LastRow - 1000 , could this lead to a negative WeekNumber value? — K_B
– K_B, Commented Aug 20, 2013 at 9:30
Which line is giving you the error? And what error exactly do you get? — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 20, 2013 at 10:35
I checked the number, I get a string variable value of "E4414", which I'm now trying to input instead of "E1" or whatever explicit expression I use for the range. — vicjun
– vicjun, Commented Aug 20, 2013 at 10:44
I get an "Unkonwn runtime error" on the .SetSourceData line, code 800A03EC, Microsoft VBScript runtime error' — vicjun
– vicjun, Commented Aug 20, 2013 at 10:45

user2140173 · Accepted Answer · 2013-08-20 11:44:34Z

2

.SetSourceData objWorksheet.Range(Rowstring,"F" & LastRow), xlColumns

where

WeekNumber = LastRow - 1000
RowString = "E" & WeekNumber

gives

...Range("E4414","F" & LastRow)...

and I think you are most likely after

...Range("E4414:F" & LastRow)...

so you need to replace the , comma with a : semicolon

rebuild your RowString variable like this

RowString = "E" & weekNumber & ":"

and the entire statement

.SetSourceData objWorksheet.Range(Rowstring & "F" & LastRow), xlColumns

' PlotBy:=xlColumns for VBA

answered Aug 20, 2013 at 11:44

user2140173

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBS for excel: Using a script variable in Range selection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related