Applying Offset to Range in VBA

[marston.gould]

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

 

[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