Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Unlock protected blocks in Siemens SIMATIC Step 7

Recently I'd been called by Hindalco's Fabrication Plant division to unlock the protected blocks in Siemens SIMATIC Step 7. They were in need to unlock those blocks since an year because of 1 million Rupees of loss per month. They want to re-program those blocks but it was locked by the man who'd done the setup. From the people working in that department, I came to know that they were trying to call that man (someone from Italy) right here but he's not coming. Actually, what he'd done was that he'd locked some of the blocks and deleted the source file. And Siemens didn't provide any feature to unlock. Department people also told me that even the people working in Siemens don't know how to do it. Being a software engineer I know that any thing can be reverse engineered. So I took up the challenge. How did I unlocked the blocks? The first thing I'd done was searched about this software at Google and read about what is this software all about. Aft
50 comments
Read more

JS: The complete code example of Crypto.js (DES)

For one of the project I was trying to use crypto.js but I found that the Quick-start Guide have some deficiency in terms of library usage. So I am writing it here as a useful note for memory recap. <script src="http://crypto-js.googlecode.com/svn/tags/3.1.2/build/rollups/tripledes.js"></script> <script> var encrypted = CryptoJS.DES.encrypt("The secret message", "secret_key"); var e_msg = encrypted.toString(); console.log(e_msg); var decrypted = CryptoJS.DES.decrypt(e_msg, "secret_key"); var d_msg = decrypted.toString(CryptoJS.enc.Utf8); console.log(d_msg); </script>
4 comments
Read more

How to convert JIRA story into sub-task or defect?

Many times we entangled in the situation where we have made a  story  in JIRA but later on realised that it should have to be  defect  or in other case,  sub-task  of another  story . Story → Sub-task So the workaround for converting the story into defect is given below: Open your  story Click on  more  option Click on the  Convert to sub-task  option in the dropdown You would be asked to choose  Parent  story, so chose relevant story After submit, your  story  gets converted into  sub-task Story → Defect Now if you want the story to be converted into defect, then you should first convert it into sub-task. Thereafter, you can convert that sub-task into defect as given below: Open the  sub-task Click on  more  option Click on the  Convert to issue  option in the dropdown You would be asked to fill up relevant fields required for raising a  defect , fill them up as required After submit, your  sub-task  gets converted into  defect .
Post a Comment
Read more