Error 2015 with Application.Evaluate

[Jeff]

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 procedure:
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]

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

[Jeff]

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 procedure:
> > 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]

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