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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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