2nd Posting - Need Help on VBA Procedure
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
|