VBA vlookup code error "Run Time Error 429"

[Dokat]

I have been using below vba code to vlookup between 2 worksheets for very large data set. It's been working fine until today where it started giving me run time error 429..

Code vlookup value in "Append worksheet" column D (4th column) in "DG Weekly Reporting - All Item Master RDH" table and return the values in H,K,LM,N...columns.

Does anyone come across similar issue where vba code all sudden not working and giving "Run-time error message '429'. ActiveX component cant create object"

When i debug it highlights below line in yellow.

"Set fWs = ThisWorkbook.Sheets("Append")"

Sub Vlookup()
    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("G:\USNSH_DG\Reports\Segmentation\DG Weekly Reporting - All Item Master RDH.xlsx")
    Set sWs = sWb.Sheets("DG Weekly Reporting - All Item ")
   Set fWs = ThisWorkbook.Sheets("Append")
    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 = 20 To 27
        Set outputCol = fWs.Range(fWs.Cells(2, i), fWs.Cells(flRow, i))
        Select Case i
            Case 20
                Set luVal = sWs.Range("H2:H" & slRow)
            Case 21
                Set luVal = sWs.Range("K2:K" & slRow)
            Case 22
                Set luVal = sWs.Range("L2:L" & slRow)
            Case 23
                Set luVal = sWs.Range("M2:M" & slRow)
            Case 24
                Set luVal = sWs.Range("N2:N" & slRow)
            Case 25
                Set luVal = sWs.Range("P2:P" & slRow)
            Case 26
                Set luVal = sWs.Range("Q2:Q" & slRow)
            Case 27
                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

Thank you

 

[Hui]

I'd check the following

1. Close Excel and then open the Task manager in Windows (Right click on the Task Bar and select Task manager), check that Excel is closed and there isn't another version of excel running, if there is close it

2. Restart the PC

3. Close all the Internet Browsers before opening Excel