How to use variables to define range
 

I have been digging through this forum for the last day or so in hopes
of finding a solution so as not to repeat a question, but have been
unsuccessful.

I am trying to define a range via variables with (row,column) format
instead of explicitly naming the range.

i.e.- set myrange =
worksheets("sheet1").range(row1,column1:row2,colum n2)
where row1, row2, column1, column2 are variables

instead of set myrange = worksheets("sheet1").range("a1:b2")

I can't seem to get the syntax correct, and I cannot figure out what I
am missing.
I can name it explicitly without a problem, but my range changes
constantly and I don't want to have to change the code everytime. Any
help would be greatly appreciated.

Thanks in advance.

Drew


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=395323

How to use variables to define range
 

Something like this...

Sub Test()
Dim myRange As Range

With Sheets("Sheet1")
Set myRange = Range(.Cells(1, 1), .Cells(2, 2))
End With

myRange.Select
End Sub
--
HTH...

Jim Thomlinson


"Dolemite" wrote:


I have been digging through this forum for the last day or so in hopes
of finding a solution so as not to repeat a question, but have been
unsuccessful.

I am trying to define a range via variables with (row,column) format
instead of explicitly naming the range.

i.e.- set myrange =
worksheets("sheet1").range(row1,column1:row2,colum n2)
where row1, row2, column1, column2 are variables

instead of set myrange = worksheets("sheet1").range("a1:b2")

I can't seem to get the syntax correct, and I cannot figure out what I
am missing.
I can name it explicitly without a problem, but my range changes
constantly and I don't want to have to change the code everytime. Any
help would be greatly appreciated.

Thanks in advance.

Drew


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=395323

How to use variables to define range
 

I don't know if the following sample helps:

Dim tstring As String
Wsheet.Select
If Wsheet.Range(fromcol & CStr(x)).Value < "NULL" And _
Wsheet.Range(tocol & CStr(x)).Value < "NULL" Then
Fvalue = Wsheet.Range(fromcol & CStr(x)).Value - Wsheet.Range(tocol &
CStr(x)).Value
tstring = fromcol & CStr(x) & ":" & tocol & CStr(x)
If Fvalue 500 Or Fvalue < -500 Then
Range(tstring).Select
Selection.Interior.ColorIndex = 3
Selection.Font.Bold = True

"Dolemite" wrote:


I have been digging through this forum for the last day or so in hopes
of finding a solution so as not to repeat a question, but have been
unsuccessful.

I am trying to define a range via variables with (row,column) format
instead of explicitly naming the range.

i.e.- set myrange =
worksheets("sheet1").range(row1,column1:row2,colum n2)
where row1, row2, column1, column2 are variables

instead of set myrange = worksheets("sheet1").range("a1:b2")

I can't seem to get the syntax correct, and I cannot figure out what I
am missing.
I can name it explicitly without a problem, but my range changes
constantly and I don't want to have to change the code everytime. Any
help would be greatly appreciated.

Thanks in advance.

Drew


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=395323

How to use variables to define range
 

Thank you very much! Everything is working great...so far

--
Dolemit
-----------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...fo&userid=2613
View this thread: http://www.excelforum.com/showthread.php?threadid=39532

How to use variables to define range
 

Or, if the code is in a general module

Dim myRange As Range
Set myRange = Sheets("Sheet1").Range("A1")
Range(myRange(1, 1), myRange(2, 2)).Select

Because of limitations on the Select Method, Sheet1 must be the active
sheet, both with the above and with Jim Thomlinson's suggested code below.

Alan Beban

Jim Thomlinson wrote:
Something like this...

Sub Test()
Dim myRange As Range

With Sheets("Sheet1")
Set myRange = Range(.Cells(1, 1), .Cells(2, 2))
End With

myRange.Select
End Sub