2nd Posting - Need Help on VBA Procedure

Jeff · 05-25-2006, 10:40 AM

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

Barb Reinhardt · 05-25-2006, 11:10 AM

You'll get a quicker response if you post this to the programming group.

microsoft.public.excel.programming

"Jeff" wrote:

> 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
>

Bob Phillips · 05-25-2006, 11:10 AM

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
>

Jeff · 05-25-2006, 11:50 AM

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
> >
>
>
>

Bob Phillips · 05-25-2006, 12:00 PM

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
> > >
> >
> >
> >

Jeff · 05-25-2006, 12:20 PM

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
> > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 05-25-2006, 12:35 PM

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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Jeff · 05-25-2006, 12:45 PM

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
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob Phillips · 05-25-2006, 01:20 PM

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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >