Dat.cells(1,1) is the upper left corner of Dat

Dat.cells(0,0) is the cell, upper left of the upper left corner of Dat

go to the VBE, and make the immediate window visible.

now type in
set Dat = Range("C9:D20")

Now use ? with a command to see the results.

for example

set dat = Range("C9:D20")
? dat.Cells(1,1).Address
$C$9
? dat.Cells(0,0).Address
$B$8
? dat.Offset(1,1).Address
$D$10:$E$21
? dat.Cells(2,3).Resize(1,2).Address
$E$10:$F$10

Remember, an Offset is the same size as the source range. Using cells or
item selects from the upper left corner and is a single cell.

--
Regards,
Tom Ogilvy



Peter Williams wrote in message
om...
Hi Everyone,
I'm new to VBA, so please pardon if this is a silly question. I have
some named ranges (e.g. DATES) and I'd like to have a function with an
integer argument and a range argument that returns a subrange of the
argument range. For the full code there will be two ranges so that
just what occured on those dates is the returned range. Here are some
examples of the way that I've been trying to do this and some of the
errors that I've been able to see the code making. I'd like to know
if there is a standard way of doing this. I think I can hack up
something with sheet formula, but I would prefer to have a working
function for a little more flexiblity. Don't know if this will get me
flamed, but I am using the OS X version of excel, in case that info is
important.

Option Explicit

'test works as expected
Function test1(arg As Integer, dat As Range) As Range

Set test1 = dat.Offset(arg, 0)
End Function

'test works as expected
Function test2(arg As Integer, dat As Range) As Range

Set test2 = dat.Cells(arg + 1, 1)
End Function

'doesn't work as expected (i.e. doesn't return dat(arg:(arg + 1))
Function test3(arg As Integer, dat As Range) As Range

Set test3 = dat.Range(dat.Cells(arg + 1, 1), dat.Cells(arg + 2, 1))
End Function

'doesn't work as expected (returns something in the column I want, but
rows are messed up
'offset in dat returned is sheet offset where dat is stored from dat
start + the offset that I add

Function test4(arg As Integer, dat As Range) As Range

Set test4 = dat.Range(dat.Cells(arg + 1, 0), dat.Cells(arg + 2, 0))
End Function

Function test5(arg As Integer, dat As Range) As Range

Set test5 = dat(dat.Cells(arg + 1, 1), dat.Cells(arg + 2, 1))
End Function

Anyhelp is appreciated, thanks in advance.