ExcelBanter - Error 2015 with Application.Evaluate

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Error 2015 with Application.Evaluate (https://www.excelbanter.com/excel-programming/363412-error-2015-application-evaluate.html)

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

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

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

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