Variable in Vlookup VBA formula

pt5014 · Apr 20th 2016

Am trying to develop some code that will input a vlookup formula into a spreadsheet. Because the data will vary the lookup cell in the Vlookup formula will vary. In my sample data below am using the UCI field as the lookup value (E1) in this case. Am trying to have the user input the cell location and then give it a range name to be used in the vba code. While that works for the lookup table it does not seem t work for the lookup value. Probably a very obvious error. Any help is appreciated. Thanks

CIN HCP Ready Date UCI
9175 190 Y 4/1/2016 6814336
97506 150 Y 4/1/2016 8088152

Code

Sub SC_V()
    Dim UCI As String
    
   UCI = InputBox("Enter cell containing UCI")
    Range("AJ1") = UCI
    
    ActiveWorkbook.Names.Add Name:="Col", RefersTo:=Range("AJ1")


    Range("A1").End(xlToRight).Offset(1, -2).Select
    V1 = "=Vlookup(Col ,Table_Name ,5 , False)"
    ActiveCell.Formula = V1
    
    
End Sub

Display More

skywriter · Apr 20th 2016

Re: Variable in Vlookup VBA formula

It would be better if you uploaded a sample workbook and make sure you have the final outcome in the sample sheet so we can see what you want.

Also as a refresher because I am a little confused by your post, remember that the item you are looking up in a VLookup formula has to be in the left most column of the range of data you are looking in.

When you click reply there will be a new button labeled go advanced, click on that button then there will be a paperclip icon in the toolbar, click on that icon and follow the instructions.

Variable in Vlookup VBA formula

Participate now!

Share

Users Viewing This Thread