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