Vba Code Vlookup And Sumif Super Fast

[roykana]

Dear All Master,

I found in this forum link: https: //chandoo.org/forum/threads/vba-code-to-vlookup-between-workbooks.32161/page-2 The one who made the VBA code was Mr. Chihiro. So I want Mr. Chihiro to be able to help with this problem. What I want as follows: 1. I want the vlookup vba code to work like an excel formula so when I type the code the results appear. 2. I want the sumif code to automatically add up. I want the fastest and simplest vlookup and sumif vba code and the fastest vlookup and sumif vba code, making it easier for me to understand like Mr. Chihiro previously made. The reason I want to use Vba code is because the original data is tens of thousands of rows and using vlookup and sumif formulas, so the processor calculation is making slow and I have to wait. I attach 2 excel files namely the first RECEIVE ITEM & DELIVERY ORDER RAW MATERIALS - 2018-test and secondly the FORMULA INFORMATION. And I inserted the vlookup vba code that was made before so that it can be modified

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

Thanks

Roykana

 

[roykana]

Dear master all,

is there anyone who can help me solve this problem. The first stage is modification of the vlookup vba code first after completion, then the next vba code "sumif"?


Thanks

Roykana

 

[roykana]

Dear all master,

Can anyone help me?

Thanks

Roykana

 

[Luke M]

Per forum rules, pleae remember to include links to cross-posts

I need VBA code

The VBA code that I want is as follows: 1. I want a "vlookup" VBA code to be typed so that the desired results appear. 2. I want a "sumif" VBA code. I want the fastest vlookup and sumif vba code and the simplest code that makes it easy for me to understand. The reason I want to use Vba code...

www.vbaexpress.com

 

[roykana]

Per forum rules, pleae remember to include links to cross-posts

I need VBA code

The VBA code that I want is as follows: 1. I want a "vlookup" VBA code to be typed so that the desired results appear. 2. I want a "sumif" VBA code. I want the fastest vlookup and sumif vba code and the simplest code that makes it easy for me to understand. The reason I want to use Vba code...

www.vbaexpress.com

I did not get a solution here

 

[roykana]

I did not get a solution here

Ok, I closed my post

 

[vletm]

roykana
As You've read few times Forum Rules in previous days,
You should seen below lines there:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.