Applying Offset to Range in VBA

[[email protected]]

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?

[JonR]

Have you tried using the VBA Find Method to locate the value in Range 2?

From the help file (you'll obviously want to modify it to suit):

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

"[email protected]" 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?
>
>

[JMB]

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




"[email protected]" 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?
>
>