How do I create this range?
 

I am trying to set a range in vba, but am having trouble figuring out how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column, only the
number, like instead of column "C" I would have a number 3. So basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance

How do I create this range?
 

One way:

Dim MyRange As Variant
Dim nCol As Long
nCol = 3
MyRange = Worksheets(ActSheet).Cells(5, nCol).Resize(46, 1).Value

In article ,
monica wrote:

I am trying to set a range in vba, but am having trouble figuring out how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column, only the
number, like instead of column "C" I would have a number 3. So basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance

How do I create this range?
 

Great, that works. Now how can I do a sum on the contents of that range? I
tried doing "for each cell in MyRange" but it gives an error...

Thanks again.


"JE McGimpsey" wrote:

One way:

Dim MyRange As Variant
Dim nCol As Long
nCol = 3
MyRange = Worksheets(ActSheet).Cells(5, nCol).Resize(46, 1).Value

In article ,
monica wrote:

I am trying to set a range in vba, but am having trouble figuring out how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column, only the
number, like instead of column "C" I would have a number 3. So basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance

How do I create this range?
 

Oops, nevermind...I figured it out!

"monica" wrote:

Great, that works. Now how can I do a sum on the contents of that range? I
tried doing "for each cell in MyRange" but it gives an error...

Thanks again.


"JE McGimpsey" wrote:

One way:

Dim MyRange As Variant
Dim nCol As Long
nCol = 3
MyRange = Worksheets(ActSheet).Cells(5, nCol).Resize(46, 1).Value

In article ,
monica wrote:

I am trying to set a range in vba, but am having trouble figuring out how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column, only the
number, like instead of column "C" I would have a number 3. So basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance

How do I create this range?
 

You need to do

Set MyRange = (whatever)

otherwise you'll just get the Value of the Range on the
right-hand side, not the Range object itself. You could
also use:

Dim MyRange as Range

instead of ..as Variant, which would catch this kind of error.



monica wrote:
Great, that works. Now how can I do a sum on the contents of that range? I
tried doing "for each cell in MyRange" but it gives an error...

Thanks again.


"JE McGimpsey" wrote:

One way:

Dim MyRange As Variant
Dim nCol As Long
nCol = 3
MyRange = Worksheets(ActSheet).Cells(5, nCol).Resize(46, 1).Value

In article ,
monica wrote:

I am trying to set a range in vba, but am having trouble figuring out how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column, only the
number, like instead of column "C" I would have a number 3. So basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance

How do I create this range?
 

Now how can I do a sum on the contents of that range?
I tried doing "for each cell in MyRange" ....

Don't know if this idea will help...

Const C As Long = 3
Debug.Print _
WorksheetFunction.Sum(Range(Cells(5, C), Cells(50, C)))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"monica" wrote in message
...
Oops, nevermind...I figured it out!

"monica" wrote:

Great, that works. Now how can I do a sum on the contents of that range?
I
tried doing "for each cell in MyRange" but it gives an error...

Thanks again.


"JE McGimpsey" wrote:

One way:

Dim MyRange As Variant
Dim nCol As Long
nCol = 3
MyRange = Worksheets(ActSheet).Cells(5, nCol).Resize(46, 1).Value

In article ,
monica wrote:

I am trying to set a range in vba, but am having trouble figuring out
how to
code it.
I know the proper syntax is normally this:
MyRange = Worksheets(ActSheet).Range("C5:C50")

My problem is that I won't have the LETTER (like C) of the column,
only the
number, like instead of column "C" I would have a number 3. So
basically,
how can I replace the ("C5:C50") portion with integer type variables?

Thanks in advance