range with variable row
 

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

range doesn't have to be continous.

Range("A1,D5, E4:G8") = 5

The numbers can be replace by variable

MyRow = 4
Range("A1,D5, E" & Myrow & ":G8") = 5

"Bonobo" wrote:

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

so would this syntax be correct?

With ActiveChart.SeriesCollection.NewSeries
..Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

It is giving me an error 450: Wrong number of arguments or invalid property
assignment

What am I doing wrong?

"Joel" wrote:

range doesn't have to be continous.

Range("A1,D5, E4:G8") = 5

The numbers can be replace by variable

MyRow = 4
Range("A1,D5, E" & Myrow & ":G8") = 5

"Bonobo" wrote:

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

The commas have to be in the quoted strings

from
With ActiveChart.SeriesCollection.NewSeries
..Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

to
With ActiveChart.SeriesCollection.NewSeries
..Values = ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q"
& r)

I tested it with the following code ancd it works. With your code it gave
me an error.
r = 1
ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q" & r).Select

I'm not sure if it will work with a series collection.

"Bonobo" wrote:

so would this syntax be correct?

With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

It is giving me an error 450: Wrong number of arguments or invalid property
assignment

What am I doing wrong?

"Joel" wrote:

range doesn't have to be continous.

Range("A1,D5, E4:G8") = 5

The numbers can be replace by variable

MyRow = 4
Range("A1,D5, E" & Myrow & ":G8") = 5

"Bonobo" wrote:

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

you're right, it is giving an error in the compilation.
Would you know the syntax to include a variable in the R1C2 format?

I would like to try with the below format, but would need to replace the 13
with a variable r:

..Values = "=(ActiveSheet!R13C5, ActiveSheet!R13C8, ActiveSheet!R13C11,
ActiveSheet!R13C14, ActiveSheet!R13C17)"


"Joel" wrote:

The commas have to be in the quoted strings

from
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

to
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q"
& r)

I tested it with the following code ancd it works. With your code it gave
me an error.
r = 1
ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q" & r).Select

I'm not sure if it will work with a series collection.

"Bonobo" wrote:

so would this syntax be correct?

With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

It is giving me an error 450: Wrong number of arguments or invalid property
assignment

What am I doing wrong?

"Joel" wrote:

range doesn't have to be continous.

Range("A1,D5, E4:G8") = 5

The numbers can be replace by variable

MyRow = 4
Range("A1,D5, E" & Myrow & ":G8") = 5

"Bonobo" wrote:

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?

range with variable row
 

Simple

r = 13
..Values = "=(ActiveSheet!R" & r & "C5, ActiveSheet!R" _
& r & "C8, ActiveSheet!R" & r & _
"C11,ActiveSheet!R" & r & "C14, ActiveSheet!R" & r & "C17)"


"Bonobo" wrote:

you're right, it is giving an error in the compilation.
Would you know the syntax to include a variable in the R1C2 format?

I would like to try with the below format, but would need to replace the 13
with a variable r:

.Values = "=(ActiveSheet!R13C5, ActiveSheet!R13C8, ActiveSheet!R13C11,
ActiveSheet!R13C14, ActiveSheet!R13C17)"


"Joel" wrote:

The commas have to be in the quoted strings

from
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

to
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q"
& r)

I tested it with the following code ancd it works. With your code it gave
me an error.
r = 1
ActiveSheet.Range("E" & r & ",H" & r & ",K" & r & ",N" & r & ",Q" & r).Select

I'm not sure if it will work with a series collection.

"Bonobo" wrote:

so would this syntax be correct?

With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("E" & r, "H" & r, "K" & r, "N" & r, "Q" & r)

It is giving me an error 450: Wrong number of arguments or invalid property
assignment

What am I doing wrong?

"Joel" wrote:

range doesn't have to be continous.

Range("A1,D5, E4:G8") = 5

The numbers can be replace by variable

MyRow = 4
Range("A1,D5, E" & Myrow & ":G8") = 5

"Bonobo" wrote:

thanks!

what if I want a range of cells that are not contiguous?
Let's say a range with cells in row r and columns D, F and H (r being a loop
from 14 to lastrow )

"Joel" wrote:



set myrange = Range("D" & r & ":P" & r)

"Bonobo" wrote:

I have a macro with a loop From r = 14 to lastrow
I need to define a Range with Row r and Columns "D" to "P"

What is the correct syntax to define the range with a variable row?