Well, for instance, this in the code

sh = "'" & Left(filename, iPos) + "[" & Right(filename,
Len(filename) - iPos)

should all be on one line, as should

cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow & "C6,MATCH(1,"
& _

and

"(" & sh & "R1C5:R" & LastRow &
"C5=""Escada"")" & _

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:499C7AD2-1738-4926-A4F1-DF9D29273295@microsoft.com...
> I'm not sure understand the "wrap-around"
> --
> Regards,
> Jeff
>
>
>
> "Bob Phillips" wrote:
>
> > Did you correct the wrap-around?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with googlemail if mailing direct)
> >
> > "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> > news:662658C1-7CBD-4278-8EE4-823D0865731A@microsoft.com...
> > > Bob,
> > > Thank for you help.
> > > I have the following error msg:
> > > Run-Time Error 1004
> > > "Unable to set FormulaArray Property of the class Range"
> > > --
> > > Regards,
> > > Jeff
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Try this version instead Jeff
> > > >
> > > > Function GetValue(filename)
> > > > Dim sh As String
> > > > Dim iPos As Long
> > > > Dim cell As Range
> > > > Const LastRow As Long = 20 '10000
> > > >
> > > > iPos = InStrRev(filename, "\")
> > > > If iPos = 0 Then
> > > > sh = "'[""" & filename
> > > > Else
> > > > sh = "'" & Left(filename, iPos) + "[" & Right(filename,
> > > > Len(filename) - iPos)
> > > > End If
> > > > sh = sh & "]Sheet1'!"
> > > > Set cell = ActiveSheet.UsedRange.Offset(1, 1)
> > > > cell.FormulaArray = "=INDEX(" & sh & "R1C6:R" & LastRow &
> > "C6,MATCH(1,"
> > > > & _
> > > > "(" & sh & "R1C1:R" & LastRow &
"C1=20)*"
> > & _
> > > > "(" & sh & "R1C2:R" & LastRow &
"C2=6)*" &
> > _
> > > > "(" & sh & "R1C3:R" & LastRow &
> > "C3=""F"")*" &
> > > > _
> > > > "(" & sh & "R1C5:R" & LastRow &
> > > > "C5=""Escada"")" & _
> > > > ",0))"
> > > > GetValue = cell.Value
> > > > cell.Clear
> > > >
> > > > End Function
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace somewhere in email address with googlemail if mailing
direct)
> > > >
> > > > "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> > > > news:83482C97-4845-4BE7-87E8-21B19F1A2CA2@microsoft.com...
> > > > > Hi Bob,
> > > > > I have the following msg for mtchValue = Error 2015.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Jeff
> > > > >
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Not tested, but try this
> > > > > >
> > > > > > Function GetValue(filename)
> > > > > > Dim mtchValue
> > > > > > Dim sh As String
> > > > > >
> > > > > > sh = "'[" & filename & "]May'!"
> > > > > > mtchValue = Application.Evaluate( _
> > > > > > "MATCH(1,(" & sh & "A1:A10000=20)*" & _
> > > > > > "(" & sh & "B1:B10000=6)*" & _
> > > > > > "(" & sh & "C1:C10000=""F"")*" & _
> > > > > > "(" & sh & "E1:E10000=""Escada""),0))")
> > > > > > If Not IsError(mtchValue) Then
> > > > > > GetValue = Application.Evaluate("Index(" & sh &
"F1:F10000,"
> > &
> > > > > > mtchValue & ")")
> > > > > > End If
> > > > > >
> > > > > > End Function
> > > > > >
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace somewhere in email address with googlemail if mailing
> > direct)
> > > > > >
> > > > > > "Jeff" <Jeff@discussions.microsoft.com> wrote in message
> > > > > > news:2B42A2EE-E351-4965-BB2B-0E0CCC2624B1@microsoft.com...
> > > > > > > Hello,
> > > > > > >
> > > > > > > I have the following hardcoded function in a worksheet:
> > > > > > >
> > > > > > >
> > > > > > > =IF(ISNA(MATCH(1,('[Option 11
CSV.xls]May'!A1:A10000=20)*('[Option
> > 11
> > > > > > > CSV.xls]May'!B1:B10000=6)* ('[Option 11
> > > > > > CSV.xls]May'!C1:C10000="F")*('[Option
> > > > > > > 11 CSV.xls]May'!E1:E10000="Escada"),0)),0,INDEX('[Option 11
> > > > > > > CSV.xls]May'!F1:F10000,MATCH(1,('[Option 11
> > > > > > > CSV.xls]May'!A1:A10000=20)*('[Option 11
> > > > > > CSV.xls]May'!B1:B10000=6)*('[Option
> > > > > > > 11 CSV.xls]May'!C1:C10000="F")*('[Option 11
> > > > > > > CSV.xls]May'!E1:E10000="Escada"),0)))
> > > > > > >
> > > > > > >
> > > > > > > I urgently need to have this function as a VBA procedure, it
needs
> > to
> > > > be
> > > > > > > included in an existing VBA macro. Also, the name of file
(Option
> > 11
> > > > > > > CSV.xls)wil change, so can we have the option of selecting the
> > file to
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Jeff
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >