Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 2015 with Application.Evaluate
Hello,
If someone could help me correct my VBA procedure. I'm lost and I don't know how to fix it. I get #value error. Here's an example of my spreadsheet: A B C D E F G 20 6 F E Escada 1,940 495,866 Here's my VBA procedu 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 Range("S1").Select ActiveCell.Value = getvalue 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 -- Regards, Jeff |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 2015 | Excel Programming | |||
N/A # and error 2015 | Excel Programming | |||
error 2015 performing vlookup | Excel Programming | |||
Error 2015 | Excel Programming | |||
Error 2015 from ConvertFormula | Excel Programming |