I am trying to figure this one out. I want to create a range on a sheet. I
know the row and column numbers for the upper left and the bottom right. I've
tried the following without any luck but I think that I am close:

-- begin code --
sheetOfInterest = "Sheet1"
topRow = 5
topColumn = 6
bottomRow = 10
bottomColumn = 20

rem THIS DOES NOT WORK
newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
topColumn), Cells(bottomRow, BottomColumn))
-- end code --

Once I get this working then I can get the rest done .

Thanks,

Mike McCollister

Sub mike()
Set r = Range(Cells(5, 6), Cells(10, 20))
MsgBox (r.Address)
r.Select
End Sub

--
Gary''s Student
gsnu200710


"Mike McCollister" wrote:

> I am trying to figure this one out. I want to create a range on a sheet. I
> know the row and column numbers for the upper left and the bottom right. I've
> tried the following without any luck but I think that I am close:
>
> -- begin code --
> sheetOfInterest = "Sheet1"
> topRow = 5
> topColumn = 6
> bottomRow = 10
> bottomColumn = 20
>
> rem THIS DOES NOT WORK
> newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
> topColumn), Cells(bottomRow, BottomColumn))
> -- end code --
>
> Once I get this working then I can get the rest done .
>
> Thanks,
>
> Mike McCollister

Thanks a lot. I knew that I was close. I ended up using this:

set r = ThisWorkbook.Worksheets("Sheet1").Range(Cells(5, 6), Cells(10, 20))

Mike

"Gary''s Student" wrote:

> Sub mike()
> Set r = Range(Cells(5, 6), Cells(10, 20))
> MsgBox (r.Address)
> r.Select
> End Sub
>
> --
> Gary''s Student
> gsnu200710
>
>
> "Mike McCollister" wrote:
>
> > I am trying to figure this one out. I want to create a range on a sheet. I
> > know the row and column numbers for the upper left and the bottom right. I've
> > tried the following without any luck but I think that I am close:
> >
> > -- begin code --
> > sheetOfInterest = "Sheet1"
> > topRow = 5
> > topColumn = 6
> > bottomRow = 10
> > bottomColumn = 20
> >
> > rem THIS DOES NOT WORK
> > newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
> > topColumn), Cells(bottomRow, BottomColumn))
> > -- end code --
> >
> > Once I get this working then I can get the rest done .
> >
> > Thanks,
> >
> > Mike McCollister

That'll work until "Sheet1" isn't the activesheet (and thisworkbook isn't the
activeworkbook).

You'll want to qualify your ranges:

set r = ThisWorkbook.Worksheets("Sheet1") _
.Range(ThisWorkbook.Worksheets("Sheet1").Cells(5, 6), _
ThisWorkbook.Worksheets("Sheet1").Cells(10, 20))

or to save your fingers:

with ThisWorkbook.Worksheets("Sheet1")
set r = .range(.cells(5,6),.cells(10,20))
end with

the leading dots mean that that property (or method) refers to the object in the
previous with statement.

Another way:

set r = ThisWorkbook.Worksheets("Sheet1").Range("F5").resize(6,15)

Start in F5 and resize it to 6 rows by 15 columns (if I subtracted correctly!).



Mike McCollister wrote:
>
> Thanks a lot. I knew that I was close. I ended up using this:
>
> set r = ThisWorkbook.Worksheets("Sheet1").Range(Cells(5, 6), Cells(10, 20))
>
> Mike
>
> "Gary''s Student" wrote:
>
> > Sub mike()
> > Set r = Range(Cells(5, 6), Cells(10, 20))
> > MsgBox (r.Address)
> > r.Select
> > End Sub
> >
> > --
> > Gary''s Student
> > gsnu200710
> >
> >
> > "Mike McCollister" wrote:
> >
> > > I am trying to figure this one out. I want to create a range on a sheet. I
> > > know the row and column numbers for the upper left and the bottom right. I've
> > > tried the following without any luck but I think that I am close:
> > >
> > > -- begin code --
> > > sheetOfInterest = "Sheet1"
> > > topRow = 5
> > > topColumn = 6
> > > bottomRow = 10
> > > bottomColumn = 20
> > >
> > > rem THIS DOES NOT WORK
> > > newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
> > > topColumn), Cells(bottomRow, BottomColumn))
> > > -- end code --
> > >
> > > Once I get this working then I can get the rest done .
> > >
> > > Thanks,
> > >
> > > Mike McCollister

--

Dave Peterson

OK. This is strange. I am trying to add this to an existing function. When I
put this in:

Set r3 = ThisWorkbook.Worksheets("Sheet1").range(Cells(15, 16),
Cells(110, 120))

"Range" is not capitalized. If I create a new spreadsheet then it works OK.
Any idea how to get this to work with the existing spreadsheet?

Thanks,

Mike


"Gary''s Student" <(E-Mail Removed)> wrote in message
news:A1A8032A-9138-4974-896A-(E-Mail Removed)...
> Sub mike()
> Set r = Range(Cells(5, 6), Cells(10, 20))
> MsgBox (r.Address)
> r.Select
> End Sub
>
> --
> Gary''s Student
> gsnu200710
>
>
> "Mike McCollister" wrote:
>
>> I am trying to figure this one out. I want to create a range on a sheet.
>> I
>> know the row and column numbers for the upper left and the bottom right.
>> I've
>> tried the following without any luck but I think that I am close:
>>
>> -- begin code --
>> sheetOfInterest = "Sheet1"
>> topRow = 5
>> topColumn = 6
>> bottomRow = 10
>> bottomColumn = 20
>>
>> rem THIS DOES NOT WORK
>> newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
>> topColumn), Cells(bottomRow, BottomColumn))
>> -- end code --
>>
>> Once I get this working then I can get the rest done .
>>
>> Thanks,
>>
>> Mike McCollister

This kind of code will work ok if Worksheets("Sheet1") is the active sheet and
Thisworkbook is the active workbook.

But if either isn't active, you'll get an error.

You could either write your code like:

Set r3 = ThisWorkbook.Worksheets("Sheet1") _
.range(ThisWorkbook.Worksheets("Sheet1").Cells(15, 16), _
ThisWorkbook.Worksheets("Sheet1").Cells(110, 120))

Each reference to any range is qualified with the correct sheet and correct
workbook.

But this'll kill your fingers pretty fast--and it really makes it more difficult
to read the code. Instead, you can use something like:

With ThisWorkbook.Worksheets("Sheet1")
set r3 = .range(.cells(15,16),.cells(110,120))
end with

The dot in front of all those range objects (.range and .cells) means that it
belongs to the object in the previous With statement.

Another option:

set r3 = ThisWorkbook.Worksheets("Sheet1").cells(15,16).resize(96,105)

..resize(x,y) means take the original range (.cells(15,16)) and make it x rows by
y columns.





Mike McCollister wrote:
>
> OK. This is strange. I am trying to add this to an existing function. When I
> put this in:
>
> Set r3 = ThisWorkbook.Worksheets("Sheet1").range(Cells(15, 16),
> Cells(110, 120))
>
> "Range" is not capitalized. If I create a new spreadsheet then it works OK.
> Any idea how to get this to work with the existing spreadsheet?
>
> Thanks,
>
> Mike
>
> "Gary''s Student" <(E-Mail Removed)> wrote in message
> news:A1A8032A-9138-4974-896A-(E-Mail Removed)...
> > Sub mike()
> > Set r = Range(Cells(5, 6), Cells(10, 20))
> > MsgBox (r.Address)
> > r.Select
> > End Sub
> >
> > --
> > Gary''s Student
> > gsnu200710
> >
> >
> > "Mike McCollister" wrote:
> >
> >> I am trying to figure this one out. I want to create a range on a sheet.
> >> I
> >> know the row and column numbers for the upper left and the bottom right.
> >> I've
> >> tried the following without any luck but I think that I am close:
> >>
> >> -- begin code --
> >> sheetOfInterest = "Sheet1"
> >> topRow = 5
> >> topColumn = 6
> >> bottomRow = 10
> >> bottomColumn = 20
> >>
> >> rem THIS DOES NOT WORK
> >> newRange = ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
> >> topColumn), Cells(bottomRow, BottomColumn))
> >> -- end code --
> >>
> >> Once I get this working then I can get the rest done .
> >>
> >> Thanks,
> >>
> >> Mike McCollister

--

Dave Peterson

Dave,

Thanks. That helps. However, I'm now wanting to search this range using the
Match function but I am getting no error and the VBA just exists. Here is a
portion of my code. This function is called from a different sheet and
categoryRange and monthRange are global ranges. Any idea why the MsgBox does
not return a value?

Function BudgetedSavingsDate(category As String, ws As String, categoryRange
As Range, monthRange As Range)
DescriptionColumn = 4
DateColumn = 2
mRowFirst = FirstRowInMonth(monthRange)
mRowLast = LastRowInMonth(monthRange)
cColumn = CategoryColumn(category, categoryRange)
returnValue = ""

With ThisWorkbook.Worksheets(ws)
Set r3 = .Range(.Cells(mRowFirst, DescriptionColumn),
..Cells(mRowLast, DescriptionColumn))
End With

rem THIS NEXT LINE SHOULD BE rowNum =
Application.WorksheetFunction.Match(category, r3)
MsgBox (Application.WorksheetFunction.Match(category, r3))

rem REST OF CODE GOES HERE

end function

Thanks,

Mike

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This kind of code will work ok if Worksheets("Sheet1") is the active sheet
> and
> Thisworkbook is the active workbook.
>
> But if either isn't active, you'll get an error.
>
> You could either write your code like:
>
> Set r3 = ThisWorkbook.Worksheets("Sheet1") _
> .range(ThisWorkbook.Worksheets("Sheet1").Cells(15, 16), _
> ThisWorkbook.Worksheets("Sheet1").Cells(110, 120))
>
> Each reference to any range is qualified with the correct sheet and
> correct
> workbook.
>
> But this'll kill your fingers pretty fast--and it really makes it more
> difficult
> to read the code. Instead, you can use something like:
>
> With ThisWorkbook.Worksheets("Sheet1")
> set r3 = .range(.cells(15,16),.cells(110,120))
> end with
>
> The dot in front of all those range objects (.range and .cells) means that
> it
> belongs to the object in the previous With statement.
>
> Another option:
>
> set r3 = ThisWorkbook.Worksheets("Sheet1").cells(15,16).resize(96,105)
>
> .resize(x,y) means take the original range (.cells(15,16)) and make it x
> rows by
> y columns.
>
>
>
>
>
> Mike McCollister wrote:
>>
>> OK. This is strange. I am trying to add this to an existing function.
>> When I
>> put this in:
>>
>> Set r3 = ThisWorkbook.Worksheets("Sheet1").range(Cells(15, 16),
>> Cells(110, 120))
>>
>> "Range" is not capitalized. If I create a new spreadsheet then it works
>> OK.
>> Any idea how to get this to work with the existing spreadsheet?
>>
>> Thanks,
>>
>> Mike
>>
>> "Gary''s Student" <(E-Mail Removed)> wrote in
>> message
>> news:A1A8032A-9138-4974-896A-(E-Mail Removed)...
>> > Sub mike()
>> > Set r = Range(Cells(5, 6), Cells(10, 20))
>> > MsgBox (r.Address)
>> > r.Select
>> > End Sub
>> >
>> > --
>> > Gary''s Student
>> > gsnu200710
>> >
>> >
>> > "Mike McCollister" wrote:
>> >
>> >> I am trying to figure this one out. I want to create a range on a
>> >> sheet.
>> >> I
>> >> know the row and column numbers for the upper left and the bottom
>> >> right.
>> >> I've
>> >> tried the following without any luck but I think that I am close:
>> >>
>> >> -- begin code --
>> >> sheetOfInterest = "Sheet1"
>> >> topRow = 5
>> >> topColumn = 6
>> >> bottomRow = 10
>> >> bottomColumn = 20
>> >>
>> >> rem THIS DOES NOT WORK
>> >> newRange =
>> >> ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
>> >> topColumn), Cells(bottomRow, BottomColumn))
>> >> -- end code --
>> >>
>> >> Once I get this working then I can get the rest done .
>> >>
>> >> Thanks,
>> >>
>> >> Mike McCollister
>
> --
>
> Dave Peterson

First, it's best to declare your variables.

Second, I'm guessing that you want an exact match with application.match().

Third, there's a difference in the way excel behaves with
application.worksheetfunction.match() vs application.match().

Application.worksheetfunction.match will cause a run time error if there is no
match.

You'd need to do something like:

Dim RowNum as long
on error resume next
rownum = application.worksheetfunction.match(whatever, goeshere, 0)
if err.number <> 0 then
'no match was found
else
'a match was found in row number: rownum
end if
on error resume next.

On the other hand, application.match() returns a variant that can be tested:

Dim rownum as Variant 'it can return an error
rownum = application.match(whatever, goeshere,0)
if iserror(rownum) then
'no match
else
'a match was found in rownum.
end if

Personally, I find the second version much easier to read.

So maybe this'll get you closer:

Option Explicit
Function BudgetedSavingsDate(category As String, ws As String, _
categoryRange As Range, monthRange As Range)

Dim DescriptionColumn As Long
Dim DateColumn As Long
Dim mRowFirst As Long
Dim mRowLast As Long
Dim cColumn As Long
Dim RowNum As Variant 'could be an error or a number!

DescriptionColumn = 4
DateColumn = 2
mRowFirst = FirstRowInMonth(monthRange)
mRowLast = LastRowInMonth(monthRange)
cColumn = CategoryColumn(category, categoryRange)
returnValue = ""

With ThisWorkbook.Worksheets(ws)
Set r3 = .Range(.Cells(mRowFirst, DescriptionColumn), _
.Cells(mRowLast, DescriptionColumn))
End With

RowNum = Application.Match(category, r3, 0)
If IsError(RowNum) Then
MsgBox "not found"
Else
MsgBox RowNum
End If

'REST OF CODE GOES HERE
'you can use the apostrophe to indicate a comment, too.

End Function


Mike McCollister wrote:
>
> Dave,
>
> Thanks. That helps. However, I'm now wanting to search this range using the
> Match function but I am getting no error and the VBA just exists. Here is a
> portion of my code. This function is called from a different sheet and
> categoryRange and monthRange are global ranges. Any idea why the MsgBox does
> not return a value?
>
> Function BudgetedSavingsDate(category As String, ws As String, categoryRange
> As Range, monthRange As Range)
> DescriptionColumn = 4
> DateColumn = 2
> mRowFirst = FirstRowInMonth(monthRange)
> mRowLast = LastRowInMonth(monthRange)
> cColumn = CategoryColumn(category, categoryRange)
> returnValue = ""
>
> With ThisWorkbook.Worksheets(ws)
> Set r3 = .Range(.Cells(mRowFirst, DescriptionColumn),
> .Cells(mRowLast, DescriptionColumn))
> End With
>
> rem THIS NEXT LINE SHOULD BE rowNum =
> Application.WorksheetFunction.Match(category, r3)
> MsgBox (Application.WorksheetFunction.Match(category, r3))
>
> rem REST OF CODE GOES HERE
>
> end function
>
> Thanks,
>
> Mike
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > This kind of code will work ok if Worksheets("Sheet1") is the active sheet
> > and
> > Thisworkbook is the active workbook.
> >
> > But if either isn't active, you'll get an error.
> >
> > You could either write your code like:
> >
> > Set r3 = ThisWorkbook.Worksheets("Sheet1") _
> > .range(ThisWorkbook.Worksheets("Sheet1").Cells(15, 16), _
> > ThisWorkbook.Worksheets("Sheet1").Cells(110, 120))
> >
> > Each reference to any range is qualified with the correct sheet and
> > correct
> > workbook.
> >
> > But this'll kill your fingers pretty fast--and it really makes it more
> > difficult
> > to read the code. Instead, you can use something like:
> >
> > With ThisWorkbook.Worksheets("Sheet1")
> > set r3 = .range(.cells(15,16),.cells(110,120))
> > end with
> >
> > The dot in front of all those range objects (.range and .cells) means that
> > it
> > belongs to the object in the previous With statement.
> >
> > Another option:
> >
> > set r3 = ThisWorkbook.Worksheets("Sheet1").cells(15,16).resize(96,105)
> >
> > .resize(x,y) means take the original range (.cells(15,16)) and make it x
> > rows by
> > y columns.
> >
> >
> >
> >
> >
> > Mike McCollister wrote:
> >>
> >> OK. This is strange. I am trying to add this to an existing function.
> >> When I
> >> put this in:
> >>
> >> Set r3 = ThisWorkbook.Worksheets("Sheet1").range(Cells(15, 16),
> >> Cells(110, 120))
> >>
> >> "Range" is not capitalized. If I create a new spreadsheet then it works
> >> OK.
> >> Any idea how to get this to work with the existing spreadsheet?
> >>
> >> Thanks,
> >>
> >> Mike
> >>
> >> "Gary''s Student" <(E-Mail Removed)> wrote in
> >> message
> >> news:A1A8032A-9138-4974-896A-(E-Mail Removed)...
> >> > Sub mike()
> >> > Set r = Range(Cells(5, 6), Cells(10, 20))
> >> > MsgBox (r.Address)
> >> > r.Select
> >> > End Sub
> >> >
> >> > --
> >> > Gary''s Student
> >> > gsnu200710
> >> >
> >> >
> >> > "Mike McCollister" wrote:
> >> >
> >> >> I am trying to figure this one out. I want to create a range on a
> >> >> sheet.
> >> >> I
> >> >> know the row and column numbers for the upper left and the bottom
> >> >> right.
> >> >> I've
> >> >> tried the following without any luck but I think that I am close:
> >> >>
> >> >> -- begin code --
> >> >> sheetOfInterest = "Sheet1"
> >> >> topRow = 5
> >> >> topColumn = 6
> >> >> bottomRow = 10
> >> >> bottomColumn = 20
> >> >>
> >> >> rem THIS DOES NOT WORK
> >> >> newRange =
> >> >> ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
> >> >> topColumn), Cells(bottomRow, BottomColumn))
> >> >> -- end code --
> >> >>
> >> >> Once I get this working then I can get the rest done .
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Mike McCollister
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave,

Thanks for all of your help. That made all of the difference in the world.
Addinig "Option Explicit" at the top of my VBA file helps a lot too.

Mike

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> First, it's best to declare your variables.
>
> Second, I'm guessing that you want an exact match with
> application.match().
>
> Third, there's a difference in the way excel behaves with
> application.worksheetfunction.match() vs application.match().
>
> Application.worksheetfunction.match will cause a run time error if there
> is no
> match.
>
> You'd need to do something like:
>
> Dim RowNum as long
> on error resume next
> rownum = application.worksheetfunction.match(whatever, goeshere, 0)
> if err.number <> 0 then
> 'no match was found
> else
> 'a match was found in row number: rownum
> end if
> on error resume next.
>
> On the other hand, application.match() returns a variant that can be
> tested:
>
> Dim rownum as Variant 'it can return an error
> rownum = application.match(whatever, goeshere,0)
> if iserror(rownum) then
> 'no match
> else
> 'a match was found in rownum.
> end if
>
> Personally, I find the second version much easier to read.
>
> So maybe this'll get you closer:
>
> Option Explicit
> Function BudgetedSavingsDate(category As String, ws As String, _
> categoryRange As Range, monthRange As
> Range)
>
> Dim DescriptionColumn As Long
> Dim DateColumn As Long
> Dim mRowFirst As Long
> Dim mRowLast As Long
> Dim cColumn As Long
> Dim RowNum As Variant 'could be an error or a number!
>
> DescriptionColumn = 4
> DateColumn = 2
> mRowFirst = FirstRowInMonth(monthRange)
> mRowLast = LastRowInMonth(monthRange)
> cColumn = CategoryColumn(category, categoryRange)
> returnValue = ""
>
> With ThisWorkbook.Worksheets(ws)
> Set r3 = .Range(.Cells(mRowFirst, DescriptionColumn), _
> .Cells(mRowLast, DescriptionColumn))
> End With
>
> RowNum = Application.Match(category, r3, 0)
> If IsError(RowNum) Then
> MsgBox "not found"
> Else
> MsgBox RowNum
> End If
>
> 'REST OF CODE GOES HERE
> 'you can use the apostrophe to indicate a comment, too.
>
> End Function
>
>
> Mike McCollister wrote:
>>
>> Dave,
>>
>> Thanks. That helps. However, I'm now wanting to search this range using
>> the
>> Match function but I am getting no error and the VBA just exists. Here is
>> a
>> portion of my code. This function is called from a different sheet and
>> categoryRange and monthRange are global ranges. Any idea why the MsgBox
>> does
>> not return a value?
>>
>> Function BudgetedSavingsDate(category As String, ws As String,
>> categoryRange
>> As Range, monthRange As Range)
>> DescriptionColumn = 4
>> DateColumn = 2
>> mRowFirst = FirstRowInMonth(monthRange)
>> mRowLast = LastRowInMonth(monthRange)
>> cColumn = CategoryColumn(category, categoryRange)
>> returnValue = ""
>>
>> With ThisWorkbook.Worksheets(ws)
>> Set r3 = .Range(.Cells(mRowFirst, DescriptionColumn),
>> .Cells(mRowLast, DescriptionColumn))
>> End With
>>
>> rem THIS NEXT LINE SHOULD BE rowNum =
>> Application.WorksheetFunction.Match(category, r3)
>> MsgBox (Application.WorksheetFunction.Match(category, r3))
>>
>> rem REST OF CODE GOES HERE
>>
>> end function
>>
>> Thanks,
>>
>> Mike
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > This kind of code will work ok if Worksheets("Sheet1") is the active
>> > sheet
>> > and
>> > Thisworkbook is the active workbook.
>> >
>> > But if either isn't active, you'll get an error.
>> >
>> > You could either write your code like:
>> >
>> > Set r3 = ThisWorkbook.Worksheets("Sheet1") _
>> > .range(ThisWorkbook.Worksheets("Sheet1").Cells(15, 16), _
>> > ThisWorkbook.Worksheets("Sheet1").Cells(110, 120))
>> >
>> > Each reference to any range is qualified with the correct sheet and
>> > correct
>> > workbook.
>> >
>> > But this'll kill your fingers pretty fast--and it really makes it more
>> > difficult
>> > to read the code. Instead, you can use something like:
>> >
>> > With ThisWorkbook.Worksheets("Sheet1")
>> > set r3 = .range(.cells(15,16),.cells(110,120))
>> > end with
>> >
>> > The dot in front of all those range objects (.range and .cells) means
>> > that
>> > it
>> > belongs to the object in the previous With statement.
>> >
>> > Another option:
>> >
>> > set r3 = ThisWorkbook.Worksheets("Sheet1").cells(15,16).resize(96,105)
>> >
>> > .resize(x,y) means take the original range (.cells(15,16)) and make it
>> > x
>> > rows by
>> > y columns.
>> >
>> >
>> >
>> >
>> >
>> > Mike McCollister wrote:
>> >>
>> >> OK. This is strange. I am trying to add this to an existing function.
>> >> When I
>> >> put this in:
>> >>
>> >> Set r3 = ThisWorkbook.Worksheets("Sheet1").range(Cells(15, 16),
>> >> Cells(110, 120))
>> >>
>> >> "Range" is not capitalized. If I create a new spreadsheet then it
>> >> works
>> >> OK.
>> >> Any idea how to get this to work with the existing spreadsheet?
>> >>
>> >> Thanks,
>> >>
>> >> Mike
>> >>
>> >> "Gary''s Student" <(E-Mail Removed)> wrote in
>> >> message
>> >> news:A1A8032A-9138-4974-896A-(E-Mail Removed)...
>> >> > Sub mike()
>> >> > Set r = Range(Cells(5, 6), Cells(10, 20))
>> >> > MsgBox (r.Address)
>> >> > r.Select
>> >> > End Sub
>> >> >
>> >> > --
>> >> > Gary''s Student
>> >> > gsnu200710
>> >> >
>> >> >
>> >> > "Mike McCollister" wrote:
>> >> >
>> >> >> I am trying to figure this one out. I want to create a range on a
>> >> >> sheet.
>> >> >> I
>> >> >> know the row and column numbers for the upper left and the bottom
>> >> >> right.
>> >> >> I've
>> >> >> tried the following without any luck but I think that I am close:
>> >> >>
>> >> >> -- begin code --
>> >> >> sheetOfInterest = "Sheet1"
>> >> >> topRow = 5
>> >> >> topColumn = 6
>> >> >> bottomRow = 10
>> >> >> bottomColumn = 20
>> >> >>
>> >> >> rem THIS DOES NOT WORK
>> >> >> newRange =
>> >> >> ThisWorkbook.Worksheets(sheetOfInterest).Range(Cells(topRow,
>> >> >> topColumn), Cells(bottomRow, BottomColumn))
>> >> >> -- end code --
>> >> >>
>> >> >> Once I get this working then I can get the rest done .
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Mike McCollister
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson