How Do I Create a Range in Excel VBA?

[Mike McCollister]

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

 

[Gary''s Student]

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

 

[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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

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]

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]

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