Vlookup VBA returning Blank Values


Active Member
Jan 19, 2015
Office Version
  1. 365

I have below VBA code where I have a range under "Summary" sheet column D and Lookup Table("Lookup") range column A:B and return the value to Summary Sheet Column C:C..

Basically vlookup(Summary (D:D), Lookup Table ("Lookup")A:B,2,False). Can anyone help me modify the code. Not sure why its returning blank.

VBA Code:
Sub VlookupVV()
    OptimizeVBA True
    Dim startTime As Single, endTime As Single
    startTime = Timer
    Dim sWb As Workbook
    Dim fWs As Worksheet, sWs As Worksheet
    Dim slRow As Long, flRow As Long
    Dim pSKU As Range, luVal As Range
    Dim lupSKU As Range, outputCol As Range
    Dim vlookupCol As Object
    Set sWb = Workbooks.Open("C:\Users\dokat\OneDrive - \Power BI\Trade Report\Montly Data\Segment Lookup Table.xlsx")
    Set sWs = sWb.Sheets("Lookup")
    Set fWs = ThisWorkbook.Sheets("Summary")
    slRow = sWs.Cells(Rows.Count, 1).End(xlUp).Row
    flRow = fWs.Cells(Rows.Count, 4).End(xlUp).Row
    Set pSKU = sWs.Range("D4:D" & slRow)
    Set lupSKU = fWs.Range("D4:D" & flRow)
    For i = 2 To 2
        Set outputCol = fWs.Range(fWs.Cells(3, i), fWs.Cells(flRow, i))
        Select Case i
            Case 2
                Set luVal = sWs.Range("B4:B" & slRow)
        End Select
    'Build Collection
       Set vlookupCol = BuildLookupCollection(pSKU, luVal)
    'Lookup the values
       VLookupValues lupSKU, outputCol, vlookupCol
    Next i
    endTime = Timer
    Debug.Print (endTime - startTime) & " seconds have passed [VBA]"
    OptimizeVBA False
    sWb.Close False
    Set vlookupCol = Nothing
End Sub
Function BuildLookupCollection(categories As Range, values As Range)
    Dim vlookupCol As Object, i As Long
    Set vlookupCol = CreateObject("Scripting.Dictionary")
    For i = 1 To categories.Rows.Count
        vlookupCol.Item(CStr(categories(i))) = values(i)
    Next i
    Set BuildLookupCollection = vlookupCol
End Function
Sub VLookupValues(lookupCategory As Range, lookupValues As Range, vlookupCol As Object)
    Dim i As Long, resArr() As Variant
    ReDim resArr(lookupCategory.Rows.Count, 1)
    For i = 1 To lookupCategory.Rows.Count
        resArr(i - 1, 0) = vlookupCol.Item(CStr(lookupCategory(i)))
    Next i
    lookupValues = resArr
End Sub
Sub OptimizeVBA(isOn As Boolean)
    Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
    Application.EnableEvents = Not (isOn)
    Application.ScreenUpdating = Not (isOn)
    ActiveSheet.DisplayPageBreaks = Not (isOn)
End Sub

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN


Well-known Member
Dec 8, 2006
Office Version
  1. 365
  2. 2010
Don't know about VBA but your VLOOKUP syntax looks wrong. The lookup should be one value not a range like Summary(D:D) seems to be


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Vlookup Help
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back