VBA code to vlookup between workbooks

[mdc23]

Hmm now this is opening my eyes to other possibilities. Wondering if possible to vlookup the sum of a range into the destination workbook based on a similar id value.. My first shot in case 17 not working.

Sub Lookup()
    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
    Dim i As Integer
 
    Set sWb = Workbooks.Open("C:\Users\america.xlsx")
    Set sWs = sWb.Sheets("source data")
    Set fWs = ThisWorkbook.Sheets("Destination")
 
    slRow = sWs.Cells(Rows.Count, 1).End(xlUp).Row
    flRow = fWs.Cells(Rows.Count, 1).End(xlUp).Row
 
    Set pSKU = sWs.Range("A2:A" & slRow)
    Set lupSKU = fWs.Range("A6:A" & flRow)
 
    For i = 17 To 24
    skipIt = False
    skipIt = skipIt Or (i = 18)
    skipIt = skipIt Or (i = 23)
    If Not skipIt Then
        Set outputCol = fWs.Range(fWs.Cells(6, i),fWs.Cells(flRow, i))
        Select Case i
            Case 17
                Set luVal = Application.WorksheetFunction.Sum(sWs.Range("A2:C" & slRow))
            Case 19
                Set luVal = sWs.Range("L2:L" & slRow)
            Case 20
                Set luVal = sWs.Range("M2:M" & slRow)
            Case 21
                Set luVal = sWs.Range("N2:N" & slRow)
            Case 22
                Set luVal = sWs.Range("P2:P" & slRow)
            Case 24
                Set luVal = sWs.Range("R2:R" & slRow)
        End Select
    End If
 
    '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

 

[roykana]

Here's VBA code for VLOOKUP using dictionary and array.

Sub TestVBA()
    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(ThisWorkbook.Path & "\Segmentation.xlsx")
    Set sWs = sWb.Sheets("Sheet1")
    Set fWs = ThisWorkbook.Sheets("Sheet1")
 
    slRow = sWs.Cells(Rows.Count, 4).End(xlUp).Row
    flRow = fWs.Cells(Rows.Count, 4).End(xlUp).Row
 
    Set pSKU = sWs.Range("D2:D" & slRow)
    Set lupSKU = fWs.Range("D2:D" & flRow)
 
    For i = 17 To 24
        Set outputCol = fWs.Range(fWs.Cells(2, i), fWs.Cells(flRow, i))
        Select Case i
            Case 17
                Set luVal = sWs.Range("H2:H" & slRow)
            Case 18
                Set luVal = sWs.Range("K2:K" & slRow)
            Case 19
                Set luVal = sWs.Range("L2:L" & slRow)
            Case 20
                Set luVal = sWs.Range("M2:M" & slRow)
            Case 21
                Set luVal = sWs.Range("N2:N" & slRow)
            Case 22
                Set luVal = sWs.Range("P2:P" & slRow)
            Case 23
                Set luVal = sWs.Range("Q2:Q" & slRow)
            Case 24
                Set luVal = sWs.Range("R2:R" & 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

This is based on code found in link (look in how does VBA lookup procedure work)
http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

When tested on my machine. Took less than 1 sec to return result.

Note: Both files need to be in same folder.

Hi Mr. Chihiro,
I want to ask :
1. If the Segmentation.xlsx file is in one workbook how about vba code?
2. if by typing the PrimarySKU number it will automatically appear as before if we use the formula, how about vba code?