Set range statement
 

I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" & myRow
& ":?" & myRow & "")

Thanks,
Mike.

Set range statement
 

Use the Cells method instead:

Sub Test()
Dim myRow As Long, Blank1Range As Range
Dim FirstWeek As Integer, LastWeek As Integer

FirstWeek = 3: LastWeek = 10: myRow = 3

Set Blank1Range = Range(Cells(myRow, FirstWeek), _
Cells(myRow, LastWeek))
Blank1Range.Select
End Sub

Regards,
Greg

-----Original Message-----
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" & myRow
& ":?" & myRow & "")

Thanks,
Mike.


.

Set range statement
 

Mike,

This is something I sort of struggle with too. I've always found defining
ranges from indexes to be rather cumbersome. I'd love for someone to show me
a really short, quick method.

Anyway, here's the way I would do it:
With Worksheets("Blank 1")
Set Blank1Range = Intersect(Range(.Columns(FirstWeek),
..Columns(LastWeek)), .Rows(myRow))
End With


"Mike" wrote in message
...
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" & myRow
& ":?" & myRow & "")

Thanks,
Mike.

Set range statement
 

With Worksheets("Blank 1")
Set Blank1Range = .Range(.Cells(myRow, FirstWeek), _
.Cells(myRow,LastWeek))
End With

With Cells notation, columns can be either numbers or letters. Cells(1, "F")
works as does Cells(1, 6).

Small point, but I'd also make myRow a Long, since the max an Integer can
accommodate is 32767.


"Mike" wrote in message
...
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" & myRow
& ":?" & myRow & "")

Thanks,
Mike.

Set range statement
 

Thanks, Greg!


-----Original Message-----
Use the Cells method instead:

Sub Test()
Dim myRow As Long, Blank1Range As Range
Dim FirstWeek As Integer, LastWeek As Integer

FirstWeek = 3: LastWeek = 10: myRow = 3

Set Blank1Range = Range(Cells(myRow, FirstWeek), _
Cells(myRow, LastWeek))
Blank1Range.Select
End Sub

Regards,
Greg

-----Original Message-----
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all
on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET
statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" &
myRow
& ":?" & myRow & "")

Thanks,
Mike.


.

.

Set range statement
 

Thanks, Tim!

-----Original Message-----
With Worksheets("Blank 1")
Set Blank1Range = .Range(.Cells(myRow, FirstWeek), _
.Cells(myRow,LastWeek))
End With

With Cells notation, columns can be either numbers or
letters. Cells(1, "F")
works as does Cells(1, 6).

Small point, but I'd also make myRow a Long, since the
max an Integer can
accommodate is 32767.


"Mike" wrote in
message
...
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all
on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET
statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" &
myRow
& ":?" & myRow & "")

Thanks,
Mike.




.

Set range statement
 

Thanks, Rob!


-----Original Message-----
Mike,

This is something I sort of struggle with too. I've
always found defining
ranges from indexes to be rather cumbersome. I'd love
for someone to show me
a really short, quick method.

Anyway, here's the way I would do it:
With Worksheets("Blank 1")
Set Blank1Range = Intersect(Range(.Columns
(FirstWeek),
..Columns(LastWeek)), .Rows(myRow))
End With


"Mike" wrote in
message
...
I am confused about how to specify my range given the
following variables

FirstWeek is an integer variable
LastWeek is an integer variable
myRow is an integer variable
Blank1Range is a range

I want to set Blank1Range to include cells from the
column # from FirstWeek to column# from LastWeek, all
on
row # from myRow.

I would know how to do this if I was able to determine
the column letter of a given cell, but I don't know how
to get this. Can anyone help me write the SET
statement
with this information?

Set Blank1Range = Worksheets("Blank 1").Range("?" &
myRow
& ":?" & myRow & "")

Thanks,
Mike.




.