vba help - in Creating Vlookup Function

[Mallesh23]

Hi Team,

Need help in creating UDF Function for Vlookup. which I come across daily.

Convert below formula into UDF Vlookup.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Scores.xlsx]Sheet1!R4C4:R7C5,2,0)"

Below is my attempted Code. which is not working, I am not good in function.

Sub Create_vlookupFunction()

    Dim Twbk As Workbook
    Set Twbk = ThisWorkbook
  
    Dim sht_output As Worksheet
    Set sht_output = Twbk.Worksheets(1) ' expected output in this sheet
  
  
    Dim input_wbk As Workbook
    Set input_wbk = Workbooks.Open("D:\Testing Folder\Scores.xlsx")
     
  
    Dim Input_Table As Range
    Dim TableString As String
  
    Dim sht_Input As Worksheet
    Set sht_Input = input_wbk.Worksheets(1)
  
 
   Set Input_Table = sht_Input.Range("B3:b6")
    TableString = Input_Table.Address(, , xlR1C1)  'Input Table Range Converted into String
  
  
    Dim Lookup_Column As Long
    Lookup_Column = Application.WorksheetFunction.Match("PlayerName", sht_output.Range("B3:C3"), 0) 'Look up Column
 
          
     Dim Find_Column As Long 'Column index no
    Find_Column = Application.WorksheetFunction.Match("Score", sht_Input.Range("D4:E4"), 0) 'Find Column
  
  
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Scores.xlsx]Sheet1!R4C4:R7C5,2,0)"
  
    sht_output.Range("C4:C16").formulaRICI = Vlookup(wbk, ws, TableString, Lookup_Column, Find_Column)
  
  
    End Sub


Function Vlookup(ByVal wbk_Input As Workbook, ByVal sht_Input As Worksheet, ByVal Table As String, ByVal Lookup_Column As Long, ByVal Find_Column As Long) As String

    Dim str As String

    If InStr(wbk_Input.Name, " ") = 0 Then
    'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Scores.xlsx]Sheet1!R4C4:R7C5,2,0)" 'If workbook Contains Space
      '.FormulaR1C1 = "=VLOOKUP(RC" & Lookup_Column & ",[" & wbk_Input.Name & "]" & sht_Input.Name & "!" & Table & ",Find_Column,0)"
     str = "=VLOOKUP(RC" & Lookup_Column & ",[" & wbk_Input.Name & "]" & sht_Input.Name & "!" & Table & ",Find_Column,0)"
  
  
    Else
    'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Scores.xlsx]Sheet1!R4C4:R7C5,2,0)" If workbook Contains Space
     '.FormulaR1C1 = "=VLOOKUP(RC" & Lookup_Column & ",[" & wbk_Input.Name & "]" & sht_Input.Name & "'!" & Table & ",Find_Column,0)"
     str = "=VLOOKUP(RC" & Lookup_Column & ",[" & wbk_Input.Name & "]" & sht_Input.Name & "'!" & Table & ",Find_Column,0)"
  
    End If

End Function

Table Column C to Fill.

Book16
	B	C
3	PlayerName	Score
4	Sachin
5	Sehwag
6	Dhoni
Sheet1

Table Lookup , extract data from Column E (Score)

Scores.xlsx
	D	E
3	Suppose Table1 from Closed workbook
4	PlayerName	Score
5	Sachin	100
6	Sehwag	80
7	Dhoni	45
Sheet1

Thanks in advance for your help.

Thanks
mg

 

[jasonb75]

What is wrong with the native vlookup function? Everything that you are attempting appears to be a recipe for disaster.

You will be more likely to receive help if you explain what you want to do (not how you want to do it) rather than just showing us your unsuccessful attempts.