Programmatically select data range

  • Thread starter Thread starter neilvk
  • Start date Start date
N

neilvk

Hi All,

How is it possible to select a data range by using variables?
i.e. instead of range(A:1,c:3) have range(i,j)

Here is my code, the line with the '##### raises an error:
"Invalid or unqualified reference"

'---------------------------------------------------------
Sub Macro2()

iCol = 7
iRow =1
iLeadtime = 5

Worksheets("Sheet1").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers

'############
ActiveChart.SetSourceData Source:=Range(.Cells(icol, irow)
.Cells(icol + iLeadtime, irow + 1)).Select, PlotBy:=xlRows
'############

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Stock / Date"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text
"Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stock"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
ActiveWindow.Visible = False

End Sub
'---------------------------------------------------------

Many thanks
Nei
 
It looks like you have a period in front of the first Cells use. It needs
to be removed as there is no qualifying with statement.

ActiveChart.SetSourceData Source:=Range(Cells(icol, irow),

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Also note that Cells(row, column) takes the row first, then the column.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Bob said:
It looks like you have a period in front of the first Cells use. It needs
to be removed as there is no qualifying with statement.

ActiveChart.SetSourceData Source:=Range(Cells(icol, irow),

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
Click to expand...
 
Macro Requires Debug at ****************. Can any one help?


Sub Macro1()
'
' Macro1 Macro
' Macro made by NVK

Dim iLeadtime As Integer
Dim dDate As Date
Dim iUsage As Integer
Dim iCurrentStock
Dim iRange As Integer
Dim i As Integer


'get vars
iLeadtime = (Range("C2").Value)
dDate = CDate(Range("B2").Value2)
iUsage = Range("D2").Value
iCurrentStock = Range("A2").Value

iRange = 1

'MsgBox iLeadTime
'MsgBox dDate
'MsgBox iUsage

'working days-------
Dim irow, icol
irow = 1 'row 5 where the stuff is outputted
icol = 7 'Column C where the stuff is outputted
'Worksheets("Sheet1").Cells(irow, icol).Value = dDate


'stock stuff -----------------
Dim iRow2, iCol2
iRow2 = 2 'row 2 where the stuff is outputted
iCol2 = 7 'Column g where the stuff is outputted
Worksheets("Sheet1").Cells(iRow2, iCol2).Value = iCurrentStock


While iRange <= iLeadtime 'loops until it reaches the leadtim
figure


iCol2 = iCol2 + 1 'moves to next cell


'syntax: returns cell A:6
'Worksheets("Sheet1").Cells(6, 1).Value = 1

'stock row

iCurrentStock = iCurrentStock - iUsage
Worksheets("Sheet1").Cells(iRow2, iCol2).Value
iCurrentStock

iRange = iRange + 1 'increment the counter

Wend

Call Macro2(irow, icol, iLeadtime) ' pass vars to other procedure


End Sub
Sub Macro2(ByVal irow, ByVal icol, ByVal iLeadtime)
'
' Macro2 Macro
' Macro recorded 05/05/2004 by


Worksheets("Sheet1").Activate




Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("L11")
ActiveChart.SeriesCollection.NewSeries
***********************
ActiveChart.SeriesCollection(1).Values = Range((Cells(irow + 1, icol))
(Cells(irow + 1, icol + iLeadtime)))
ActiveChart.SeriesCollection(1).XValues
Sheets("Sheet1").Range(Cells(irow, icol), Cells(irow, icol
iLeadtime))

ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Stock / Date"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text
"Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Stock"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False


End Su
 
In this line your range is unqualified:

ActiveChart.SeriesCollection(1).Values = Range((Cells(irow + 1, icol)),
(Cells(irow + 1, icol + iLeadtime)))

Try something like this:

ActiveChart.SeriesCollection(1).Values =
Sheets("Sheet1").Range((Cells(irow + 1, icol)),
(Cells(irow + 1, icol + iLeadtime)))

or even

ActiveChart.SeriesCollection(1).Values =
Sheets("Sheet1").Range((Sheets("Sheet1").Cells(irow + 1, icol)),
(Sheets("Sheet1").Cells(irow + 1, icol + iLeadtime)))

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top