LinkBack URL
About LinkBacksBob,
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
> > > >
> > >
> > >
> > >
>
>
>
Register To Reply
Bookmarks