selecting a range in a macro

[mish78]

Hi,
I am trying to select a range in a macro where the first cell position will be returned by a formula.

In excel =CELL("address",INDEX(E6:J6,1, MATCH(customer,E6:J6))) would give me $F$6 which is the location of the cell that has customer in it.

I am trying to use it in a macro but keep getting method range of object global failed. Is there any other way of selecting a range with the first cell value as a search.
e.g
Range("CELL('address',INDEX(E6:J6,1,MATCH(customer,E6:J6))):AA1000").Select

Thanks in advance

 

[Andrew Poulsom]

A couple of possibilities:

Sub Test1()
    Range(Evaluate("=CELL(""address"",INDEX(E6:J6,1, MATCH(customer,E6:J6)))") & ":AA1000").Select
    MsgBox Selection.Address
End Sub
 
Sub Test2()
    Range(Range("E6:J6").Cells(1, WorksheetFunction.Match(Range("customer"), Range("E6:J6"))).Address & ":AA1000").Select
    MsgBox Selection.Address
End Sub