multiple functions in worksheetfunction

[jerry12302]

I can have several different functions in one function in a cell in a worksheet, for example:

=INDEX(OFFSET(my1rangename,0,1),MATCH($A$1,my1rangename,0),1)

But I can't seem to do that in VBA with WorksheetFunction, the following will not work:

X = WorksheetFunction.Index(Offset(Range("my1rangename"),Match(myvariable, Range("my1rangename"), 0), 1)

I have to use multiple lines, and in this case, define a new range:

X = WorksheetFunction.Match(myvariable, Range("my1rangename"), 0)
Y = WorksheetFunction.Index(Range("mynewrange"),X, 1)

Is there a way to include multiple functions in one line of WorksheetFunction code?

 

[SydneyGeek]

Have you tried...

X = WorksheetFunction.Index(WorksheetFunction.Offset(Range("my1rangename"),WorksheetFunction.Match(myvariable, Range("my1rangename"), 0), 1)

Denis

 

[jerry12302]

Yes, I tried that, I get a run time error

Object doesn't support this property or method.

 

[jerry12302]

Is there a way to create a custom function in vba that could be called from any code in a module that would allow for this?

 

[SydneyGeek]

Jerry, by taking a step back and looking at what you need to achieve, this is one solution:

Sub AlternateOffset()
  Dim X
  
  X = Range("Range2").Cells(WorksheetFunction.Match(Range("a1"), Range("Range2"), 0), 1).Offset(0, 1)
  Debug.Print X
  
End Sub

It doesn't get around using multiple WorksheetFunction calls, instead it combines one MATCH function with the VBA Offset method.

Denis