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

Encode or decode date in 3 characters

I have developed a new way to encode/decode dates within 3 characters by using base-36 format. What is a base 36 format? Base 36 or hexatridecimal is a positional numeral system using 36 as the radix. The choice of 36 is convenient in that the digits can be represented using the Arabic numerals 0-9 and the Latin letters A-Z. In simple language, base-36 format refers to series of 0-9 followed by A-Z characters, i.e., 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ; where each character refers to the position index in series. The concept can also be represents as shown in the below table Series Character Series Character Series Character Series Character 0 0 10 A 20 K 30 U 1 1 11 B 21 L 31 V 2 2 12 C 22 M 32 W 3 3 13 D 23 N 33 X 4 4 14 E 24 O 34 Y 5 5 15 F 25 P ...
Post a Comment
Read more