How to use MATCH with a vector in VBA?

[joes]

Hello

I like to call the worksheetfunction MATCH in VBA. I am using the
formula
=MATCH(2;1/(2:2<>"")) for getting the last used column. How do I
programm that with VBA?

Application.WorksheetFunction.Match( ... ???? )

Thanks for any hints and examples

regards
Mark

 

[Dave Peterson]

I'd use something like:

Option Explicit
Sub testme()
Dim myFormula As String
Dim myRng As Range
Dim res As Variant

With ActiveSheet
Set myRng = .Rows(2)

myFormula = "=Match(2,1/(" & _
(myRng.Address(external:=True) & "<>""""))")

res = Evaluate(myFormula)
If IsError(res) Then
MsgBox "error"
Else
MsgBox res
End If
End With

End Sub