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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 2015 with Application.Evaluate
Without testing....
getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & mtchValue & ")") needs an extra comma: getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & "," & mtchValue & ")") Jeff wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 2015 with Application.Evaluate
Hi Dave,
I made the correction, but I still have the same error msg (#value) -- Regards, Jeff "Dave Peterson" wrote: Without testing.... getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & mtchValue & ")") needs an extra comma: getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & "," & mtchValue & ")") Jeff wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 2015 with Application.Evaluate
There was an error in the original thread.
mtchValue = ("MATCH(1,(" & sh & "A1:A10000=20)*" & _ "(" & sh & "B1:B10000=6)*" & "(" & sh & _ "C1:C10000=""F"")*" & "(" & sh & _ "E1:E10000=""Escada""),0)") Should have been: mtchValue = application.evaluate _ ("MATCH(1,(" & sh & "A1:A10000=20)*" & _ "(" & sh & "B1:B10000=6)*" & "(" & sh & _ "C1:C10000=""F"")*" & "(" & sh & _ "E1:E10000=""Escada""),0)") Else mtchValue is just that long string. Jeff wrote: Hi Dave, I made the correction, but I still have the same error msg (#value) -- Regards, Jeff "Dave Peterson" wrote: Without testing.... getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & mtchValue & ")") needs an extra comma: getvalue _ = Application.Evaluate("Index(" & sh & "F1:F10000" & "," & mtchValue & ")") Jeff wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
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 |