Vlookup macro

[Alka Bajaj]

Hello Expert,

I have a vlookup macro which

Current Functionality

• Can only execute on a sheet on which macro is created.
• Have to copy two reference sheet to that WB for Macro to refer

Expected Requirement

• Can run the macro code on any sheet, without copying the code to that sheet.
• If I do not copy the two reference sheet.

Code Used:
Sub lookup()
Columns("D:D").Insert Shift:=xlToRight
Columns("E:E").Insert Shift:=xlToRight
With Range("D2:D250000")
.Formula = "=VLOOKUP(C2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Value
End With
With Range("E2:E250000")
.Formula = "=VLOOKUP(D2,CSG_Desc!$A$2:$B$20,2,FALSE)"
.Value = .Value
End With
Range("D1") = "CSG ID"
Range("E1") = "CSG"
End Sub

Appreciate your guidance to execute currect requirement.

Regards,
Alka Bajaj

 

[John Davis]

Hello Expert,

I have a vlookup macro which

Current Functionality

• Can only execute on a sheet on which macro is created.
• Have to copy two reference sheet to that WB for Macro to refer

Expected Requirement

• Can run the macro code on any sheet, without copying the code to that sheet.
• If I do not copy the two reference sheet.

Code Used:
Sub lookup()
Columns("D:D").Insert Shift:=xlToRight
Columns("E:E").Insert Shift:=xlToRight
With Range("D2:D250000")
.Formula = "=VLOOKUP(C2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Value
End With
With Range("E2:E250000")
.Formula = "=VLOOKUP(D2,CSG_Desc!$A$2:$B$20,2,FALSE)"
.Value = .Value
End With
Range("D1") = "CSG ID"
Range("E1") = "CSG"
End Sub

Appreciate your guidance to execute currect requirement.

Regards,
Alka Bajaj

If the code is placed in a Module, you should be able to run it on any sheet? I'm using 2003. For 2003:

Copy your sub procedure Tools>Macro>Visual Basic Editor>Select the project name (should be the name of the workbook)>Insert>Module and paste the code.

For your second question, If I understand, I think you only need to revised your formula to indicate the workbook as well, like so:

"=VLOOKUP(C2,'[WORKBOOK NAME.xls]ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"

The workbook with the reference sheets should be open when you run the macro though.