August 11, 2010

Generate id from name in excel micro code

If you have two column data one is for Id and other is for name then you can get Id base on name data. You can paste this code in your excel micro editor and need to change variable as per your requirement.

 Sub orange(ByVal Target As Range)  
 For i = 7 To LastRow("_RegistrationDetails_1-1")  
 If Target.Address = ("$O$" & i) Then  
 Dim rng As Range  
 'set Village ID range [P]  
 Set rng = Worksheets("_RegistrationDetails_1-1").Range("$P$" & i)  
 If Target(1, 1) = "" Then  
 'clear Village Id [P]  
 rng(1, 1).Value = ""  
 'Village Id [P] Lookup from Taluka name [M] & Village name [O]  
 rng(1, 1).Value = secondarySVlookup("_RegistrationDetails_1-1", "$M$" & i, "$O$" & i, "Village")  
 End If  
 i = LastRow("_RegistrationDetails_1-1")  
 End If  
 Next i  
 End Sub