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
>