Excel VBA Type Mismatch error

[kashif.special2005]

Hi,

I am applying vlookup functionality through Dictionary object, and it is working fine if the lookup value is less than or equal to 70000, I am holding lookup data in the variable "mydata2", but if the lookup value is more than like 80000, it is giving my an "Type Mismatch" error in the below line.

Range("B2").Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)

Note:- In Dictionary object I am apply vlookup like functionality in memory and it is running fast., and vlookup is taking more time to calculate.

Sub UseDictionary()


    ' Get the range of values
    Dim rg As Range
    Dim mydata As Variant
    Dim mydata2 As Variant
    Dim mydata3() As Variant
    
    Dim mytime As Date
    
    mytime = Now
    
    mydata = Sheet1.Range("A2:B500001").Value
    mydata2 = Sheet2.Range("A2:a80000")
    
    ' Create the dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = LBound(mydata, 1) To UBound(mydata, 1)
        dict(mydata(i, 1)) = mydata(i, 2)
    Next i
    
    ReDim mydata3(1 To UBound(mydata2))


    For i = LBound(mydata2, 1) To UBound(mydata2, 1)
        mydata3(i) = dict(mydata2(i, 1))
    Next i


    Range("B2").Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)


    MsgBox Format(Now - mytime, "hh:mm:ss")


End Sub

Please help me where I am doing wrong.

Thanks
Kashif

 

[Norie]

Are there any error values in mydata3?

 

[kashif.special2005]

Hi Norie,

Thanks for reply, No there is no any error value in mydata3

Thanks
Kashif

 

[Norie]

What is in the array and what 'vlookup functionality' are you using in the code?

 

[kashif.special2005]

Hi Norie,

I just checked that in this line of code "mydata2 = Sheet2.Range("A2:a80000")", when I giving range "A2:A65000" it is working fine and I if I am giving range "A2:A66000" it is giving the same error.

Thanks
Kashif

 

[Norie]

What's the connection with this line?

    Range("B2").Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)

 

[kashif.special2005]

Just employee data of each day, basically I am automating manually task through vba, like updating inserting, and deleting data and applying some formula also, and one of formula is "vlookup" and when I am applying vlookup formula through vba it is taking lot of time, and I have read here https://excelmacromastery.com/vba-vlookup/comment-page-1/#comment-16522, that dictionary object can perform much faster than vlookup and it is working fine if the data is less than 65000, but I don't know what is the problem it is not working more than that.

Thanks
Kashif

 

[kashif.special2005]

Basically I am pasting arrays data in the range at one go.

Thanks
Kashif

 

[kashif.special2005]

Hi Norie,

I found the solution, basically the error was in below line

Previous code :- ReDim mydata3(1 To UBound(mydata2))

New code:- ReDim mydata3(1 To UBound(mydata2), 1 To 1)

Basically I am re dimensioning column also, and it is working fine.

Thanks for your time.

Thanks
Kashif

 

[kashif.special2005]

Working Code:

Sub UseDictionary()


    ' Get the range of values
    Dim rg As Range
    Dim mydata As Variant
    Dim mydata2 As Variant
    Dim mydata3() As Variant
    
    Dim mytime As Date
    
    mytime = Now
    
    mydata = Sheet1.Range("A2:B500001").Value
    mydata2 = Sheet2.Range("A2:a80001")
    
    ' Create the dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = LBound(mydata, 1) To UBound(mydata, 1)
        dict(mydata(i, 1)) = mydata(i, 2)
    Next i
    
[I][B]    ReDim mydata3(1 To UBound(mydata2), 1 To 1)[/B][/I]


    For i = LBound(mydata2, 1) To UBound(mydata2, 1)
        mydata3(i, 1) = dict(mydata2(i, 1))
    Next i


[I][B]    Range("B2").Resize(UBound(mydata3), 1).Value = mydata3[/B][/I]


    MsgBox Format(Now - mytime, "hh:mm:ss")


End Sub