Excel VBA Type Mismatch error

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
443
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.

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: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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Are there any error values in mydata3?
 
Upvote 0
What is in the array and what 'vlookup functionality' are you using in the code?
 
Upvote 0
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
 
Upvote 0
What's the connection with this line?
Code:
    Range("B2").Resize(UBound(mydata3), 1).Value = Application.Transpose(mydata3)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Working Code:

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top