Hello,
I need help,
This is my VBA macro
Sub Macro5()
Dim mtchValue As Variant
Dim getvalue As Variant
Dim sh As String
Dim fname
fname = Application.GetOpenFilename
Workbooks.Open filename:=fname
sh = "'[" & fname & "]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
Can anyone tell me why I have "Error 2015" on mtchvalue?


End Sub
--
Regards,
Jeff

fname returns the whole path/filename.

So if fName = C:\My Documents\excel\book5.xls, then this portion:

sh = "'[" & fname & "]May'!"

will be essentially:

sh = "'[C:\My Documents\excel\book5.xls]may'!"

And that's not the way excel builds that formula.

If you drop that .open statement (probably there for testing???), you'd want
your formula to look more like:

=MATCH(1,('C:\My Documents\excel\[book5.xls]May'!$A$1:$A$10=20)*....


And your formula had too many closing parentheses, too:

Option Explicit

Sub Macro5()
Dim mtchValue As Variant
Dim getvalue As Variant
Dim sh As String
Dim fname As Variant

fname = Application.GetOpenFilename
If fname = False Then
Exit Sub 'cancel
End If

sh = MakeReferenceNicer(fname)

'Workbooks.Open Filename:=fname

mtchValue = ("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 Sub
Function MakeReferenceNicer(fname) As String

'taking
'"C:\My Documents\excel\book5.xls"
'shooting for:
'"'C:\My Documents\excel\[book5.xls]May'!"

Dim iCtr As Long
Dim myStr As String

For iCtr = Len(fname) To 1 Step -1
If Mid(fname, iCtr, 1) = "\" Then
'found that last backslash
myStr = "'" & Left(fname, iCtr) & _
"[" & Mid(fname, iCtr + 1) & "]May'!"
Exit For
End If
Next iCtr

MakeReferenceNicer = myStr

End Function


Jeff wrote:

Hello,
I need help,
This is my VBA macro
Sub Macro5()
Dim mtchValue As Variant
Dim getvalue As Variant
Dim sh As String
Dim fname
fname = Application.GetOpenFilename
Workbooks.Open filename:=fname
sh = "'[" & fname & "]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
Can anyone tell me why I have "Error 2015" on mtchvalue?

End Sub
--
Regards,
Jeff

--

Dave Peterson

Jeff,
how about staying with one thread and not have 4 or 5 going in different
workgroups on the same problem.

How do you get a sheet name of MAY in a CSV file?

I suggested one solution. If your formula works in a worksheet, it should
work as suggested:

sName = "'[Option 11 CSV.xls]May'"
sform = "=IF(ISNA(MATCH(1,(XXX!A1:A10000=20)*(XXX!B1:B1000 0=6)*" & _
"(XXX!C1:C10000=""F"")*(XXX!E1:E10000=""Escada""), 0)),0,INDEX(" & _
"XXX!F1:F10000,MATCH(1,(XXX!A1:A10000=20)*(XXX!B1: B10000=6)*" & _
"(XXX!C1:C10000=""F"")*(XXX!E1:E10000=""Escada""), 0)))"
s1 = Replace(sForm,"XXX",sname)

Now s1 contains the formula as a string that can be evaluated or assigned to
the formula property of a cell.

res = Evaluate(s1)

or
ActiveCell.Formula = S1

however, your Formla is too long to use FormulaArray - it only accepts about
255 characters.

if there will only be a match on one row, then you might want to use
sumproduct in the formula placed in the cell so you don't have to use
FormulaArray.

--
Regards,
Tom Ogilvy




"Jeff" wrote:

Hello,
I need help,
This is my VBA macro
Sub Macro5()
Dim mtchValue As Variant
Dim getvalue As Variant
Dim sh As String
Dim fname
fname = Application.GetOpenFilename
Workbooks.Open filename:=fname
sh = "'[" & fname & "]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
Can anyone tell me why I have "Error 2015" on mtchvalue?


End Sub
--
Regards,
Jeff