2nd Posting - Need Help on VBA Procedure

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