returning ranges offset from argument range function call

[Peter Williams]

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(argarg + 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.

 

[Tom Ogilvy]

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("C920")

Now use ? with a command to see the results.

for example

set dat = Range("C920")
? 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.

 

[Peter Williams]

Hi Tom,

Thank you for taking up my cause, I see the difference between the
offset and the Cells methods. What really bothers me is that I think
that
the function test3 in my original post should return a 2 element range
of
the values in rows arg and arg + 1. It doesn't do that. When I try
test4 it
does give me something in the correct column, but the row offset gets
mangled (this is for dat defined to be a range in column B, which I
think
shouldn't matter at all, but output disagrees.

To sum up, if you could tell me what is wrong with the function test3
that it doesn't return the 2 element range that I want/expect it to. Or
why test4 gives strange results.

Thanks again
Pete Williams

 

[Tom Ogilvy]

You tell me what you have and what you expect and I might be able to
dat(argarg + 1)) really doesn't tell me anything, I don't know what you
are expecting.

If you say

rng = B5:C10
arg = 3

I expect

B8:C13

from

Function test3(arg As Integer, dat As Range) As Range
' some code
End Function

then I can comment.

 

[Peter Williams]

Concrete Example

In a clean worksheet insert insert 0 through 9 in cells A6 through A15.
Then select 2 empty cells and enter the formula =test3(2, A6:A15) hit
ctrl+shift+return

I think this should return 2 and 3 in the return cells, it returns 7 and
8.
So it is adding the distance from the start of the array to row 1 to the
offset making it 7 instead of 2. I don't understand why that is
happening.

Pete

 

[Tom Ogilvy]

Your problem is that you are offsetting from Dat twice.

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

Returns 2 and 3 when array entered in two cells in the same column.

Dat.Cells(arg+1,1) evaluates to A8

Dat.Cells(arg+2,1) evaluates to A9

so if you do

Dat.Range(A8:A9) this offsets you from A6

Checking from the immediate window to illustrate:

? range("A6:A15").Range("A8").Address
$A$13
? range("A6:A15").Range("A8").value
7

to get what you want, modify it as above.