VBS for excel:在范围选择中使用脚本变量 [英] VBS for excel: Using a script variable in Range selection
问题描述
我看过类似的问题,要求在Excel中使用VBA,但是我使用的是VBScript,所以有点不一样。其他问题的答案给了我一些线索,但我只是不知道如何解决这个问题。
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.
我正在使用.SetSourceData来定义要使用的范围在图中。定义从第一行到最后一行(整个表)的范围很好,但是我想使用前面定义的变量来定义一个动态范围。所以这样做:
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
其中LastRow定义为 LastRow = objWorksheet.UsedRange.Rows.Count
。现在我想做的是使用从最后一行开始的范围,返回几行。所以我的想法是这样做:
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
其中RowString被定义为
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
提前谢谢
推荐答案
.SetSourceData objWorksheet.Range(Rowstring,"F" & LastRow), xlColumns
其中
WeekNumber = LastRow - 1000
RowString = "E" & WeekNumber
给
...Range("E4414","F" & LastRow)...
,我认为你最有可能在
...Range("E4414:F" & LastRow)...
所以你需要替换,
逗号与:
分号
so you need to replace the ,
comma with a :
semicolon
重建您的 RowString
这样的变量
RowString = "E" & weekNumber & ":"
和整个陈述
.SetSourceData objWorksheet.Range(Rowstring & "F" & LastRow), xlColumns
' PlotBy:=xlColumns for VBA
这篇关于VBS for excel:在范围选择中使用脚本变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!