Set range statement

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

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.
 
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
 
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
 
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.
 
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

.
Click to expand...
 
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.





.
Click to expand...
 
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





.
Click to expand...
 
Back
Top