Last week one student sent this question over email, so sharing it now..
I am a student of Analytix Lab and I have attended your class held on 3rd & 9th January 2016 on Excel Macro.
Few days ago, I had appeared in an interview for the post of “Analyst” in “Indus Tower”. In final round they asked me to prepare a excel macro program which was:-
There is an excel file which consist data of 2500 customers having columns of Customer ID, name, address, tariff plan, billing date etc. and there is another excel file which have few customer ID (lets assume 50) whom have filed complaint regarding services. So, the requirement was: as soon as we write customer ID in second excel file, the respective name should appear automatically in front of that ID.
There was condition of not to use VLOOKUP and Record Macro by any how.
I am looking forward to hearing from you for the solution or idea.
Gaurav Chandra Srivastava
- Workbook is closed from where we want to fetch the result.
- Run macro when user enter the customer ID in workbook 2 to print customer name.
- If workbook is closed we cannot fetch value through vlookup function in vba, so we will write here vlookup formula in VBA.
- To Run macro on Value Change, we will write our code in “Worksheet_Chnage” event
Private Sub Worksheet_Change(ByVal Target As Range)
Now let’s perform this, we have two workbooks named as Workbook1 and Workbook2.
Workbook1 = File containing information Customer ID, name, address, tariff plan, billing date etc.
Workbook2 = File containing Customer ID’s, whom have filed complaint regarding services
Note: We assuming here that we have data in sheet1 of both workbooks.
Based of customer ID we want to print customer name from workbook1 that means we want to run macro on “Cell Value Changed” event.
You can use below code to perform the task, only need to update your workbook address in below Vlookup function.
Private Sub Worksheet_Change(ByVal Target As Range) Dim str As String Dim val As String If Not Intersect(Target, Range("A2:A1048576")) Is Nothing Then val = Target.Address Target.Offset(0, 1).Formula = "=VLOOKUP(" & val & ",'C:\Users\lokesh paliwal\Desktop\[Book11.xlsx]Sheet1'!$A:$B,2,0)" Target.Offset(0, 1).Copy Target.Offset(0, 1).PasteSpecial xlValues Application.CutCopyMode = False End If End Sub