View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB
 
Posts: n/a
Default Applying Offset to Range in VBA

VBA has its own offset method.

Range("A1").Offset(3, 0)
refers to cell A4 ( Offset(0,0) is cell A1)

Range("A1") (4, 1)
also refers to cell A4 (cell A1 is 1,1 so the row and column offset will be
1 more than if you use the offset method shown above)

With Worksheets("Sheet1")
.Range("A1", .Range("A1").Offset(3,0))
End With

refers to Sheet1!A1:A4




" wrote:

I'm trying to convert a rather complicated formula into a custom
function.

Effectively, what the function does is it takes two values and uses the
worksheet function Match to locate the position of the values from a
sequentially increasing set in a range that are just smaller than the
two values being tested and then makes a comparison of those two
location values.

E.g. A = Match(value1, range1,1), B= Match(value2,range1,1); C = B - A

There are different procedures dependent on whether C = 0, 1, or is
greater than 1.
In each case, the procedure requires (in the workbook formula, not the
custom function - yet?)
the use of an offset function applied to another range of values
(range2) of similar length to range1. I do this by using the reference
cell that is the first in the range of range2 and the offset a certain
distance based on the values of A,B, and C and then perform some simple
math functions.

How do I reference the first cell location in range2 and use the offset
formula within a custom function? I'm using the
Application.WorksheetFunction.Offset, but it doesn't seem to work.

Thoughts?