Vlookup excel via vb script - cannot pass PREFERRED sheet to retrieve information

Posted on 2016-09-01
Last Modified: 2016-09-02
Hi Experts,

I'm a beginner in vb script and i came across a script(attached below) below to vlookup two files. I made few changes to vlookup 2 sheets in a single file.
Dim objExcel, objWB1, objWB2, objWS1, objWS2

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

file1 = InputBox("Enter the path\name to the excel file:")
Sheet_1 = InputBox("Enter the sheet name to retrieve information from:")
Sheet_2 = InputBox("Enter the lookup sheet name:")

Set objWB1 = objExcel.Workbooks.Open(file1)
Set objWS1 = objWB1.Sheets(Sheet_1)

'Set objWB2 = objExcel.Workbooks.Open("C:\My Documents\Microsoft Scripts\Amended Script\New\file2.xlsx")
Set objWS2 = objWB1.Sheets(Sheet_2)


objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Formula = "=VLOOKUP($A:$A,?????!$A:$A,1,false)"
objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Value


'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = "=VLOOKUP(INDEX($A:$A,ROW()),file1.xlsx!$A:$A,1,false)"
'objWS1.Range("B1:B" & objWS1.UsedRange.Rows.Count).Formula = objWS2.Range("B1:B" & objWS1.UsedRange.Rows.Count).Value

objWB1.Close false
'objWB2.Close false


I am not able to choose the sheet i want to retrieve the information from, thus i have inserted "?????" in the vlookup formula because im not sure what variable to insert. I tried using object set objWS1 and Sheet_1, but did not work. I want to be able to vlookup any sheet and return any information(column) to the vlookup sheet in the in the excel ,thus i have inserted inputbox to prompt user to input the sheet name. I have attached a file1.xlsx for testing purposes. Appreciate your help on this. Let me know if you need more information
Question by:Adrian Raj
LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 41781078
I think this is what you are looking for

objWS2.Range("B1:B" & objWS2.UsedRange.Rows.Count).Formula = "=VLOOKUP($A:$A,'" & & "'!$A:$A,1,false)"

Author Comment

by:Adrian Raj
ID: 41781468
hi Saqib,

nicely done, it works. I just have to change to because the table array is from Sheet_1. Thanks for the help.

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now