Pages

Wednesday, September 11, 2013

XLSM: A macro function for excel-sheet to find the Value of a selected Key

Type the text below in the excel-sheet formula bar

=Trouver(ResultSheet!A1, SourceSheet!A1, SourceSheet!A20, SourceSheet!B1)

Macro:
'Name: Trouver (Find)
'Creator: Abhishek Kumar
'On: 16 May 2011
'For: Vijay Kumar Mittal
'Email: akbittu@gmail.com

Public Function Trouver(searchKey, fromKeyRef, toKeyRef, getValFrom)
   Dim rCell As Range, foundVal As String
   For Each rCell In Range(fromKeyRef, toKeyRef)
        If (searchKey = rCell.Value) Then
            foundVal = Sheets(getValFrom.Parent.Name).Cells(rCell.Row, getValFrom.Column)
        End If
   Next rCell
   Trouver = foundVal
End Function

No comments:

Post a Comment