PDA

View Full Version : Is it possible to automate vlookups functions? If so, how can it be done?



wedd
02-28-2012, 09:59 PM
Hi, is it possible to automate a vlookup function in ms excel, if so, how can this be done? Would this require using visual basic? If so, would you know of any sample code to do this? I am a beginner/intermediate user of visual basic...but I would like to improve my knowledge and understanding of excel and visual basic.



Thanks for your contributions?:friends:

Xrull
02-29-2012, 05:41 AM
Wedd:

Automating Vlookup can be done. I've attached a file with a simple example.

In the future, please provide an example of the type of process you are thinking of automating because the example I have provided, might not be what you have in mind. And persons providing a solution don't have to create the data for you.

Type VBA vlookup, and you'll find numerous examples on this site.

Sub mcr_Vlookup()

'/ Identify the worksheet with the data you intend to pull the result from
Dim wks As Worksheet
Set wks = Sheets("Data_Base")

'/ Define what you are looking for
Dim vlookup_value As String
vlookup_value = Range("D1").Value

'/ Set the table array
Dim table_array As Range
Set table_array = wks.Range("A:B")

'/ The column that will be used to return the result
Dim column_index_number As Integer
column_index_number = 2

'/ Result in a message box
MsgBox WorksheetFunction.VLookup(vlookup_value, table_array, column_index_number, False)


End Sub

Xrull
02-29-2012, 05:42 AM
I've attached the file I created.

dan blouch
03-10-2014, 11:05 AM
I want to select a cell on one sheet and have it populate a cell on another sheet. From J8 to J804. This what I am using but I have to type it in each cell and it is visible in the cell. is there a way of doing this in macro?

example:
=CVI!J8+VLOOKUP(CVI!J8:J804,CVI!AK14:AL163,2,FALSE)