• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Vlookup for large data

sguna1982

Member
Hi Team,

Need favour about excel VBA for Vlookup function, Since its huge data size, its taking so much of time. So i am looking for excel VBA queries for run vlookup formula. I have 2 excel work books in the data size of around 300000 rows for both files, i am lookuping data from one excel to another excel using vlookup formula, while run the formula its taking more then hour, then paste as values will take again on hour. So need your help to fix this, thanks in advance.

Lookup_value = [Book1.xlsx]Sheet1!A2
Table_array = [Book2.xlsx]Sheet1!$A$2:$B$16
Col_index = 2
Range_lookup = FALSE

After execute this formula, i paste the values from formulas.

Thanks,
Guna
 

Attachments

sguna1982

Member
Hi Marc,

Thanks for your response. I am using this suggestion, but the problems are , 1). I need to sort the entire workbook and 2). Again copy and paste the formulas into values is taking time again. Please suggest.

Thanks
Guna
 

Marc L

Excel Ninja
No matters as you can fill the formula by code …​
It's a common issue when using Excel for a huge database as it's just a spreadsheet.​
As any database software is at least 50 times faster !!​
Other ways you can see in VBA inner help or in blog / threads from this forum :​
 

sguna1982

Member
Hi Marc,

Thanks for your prompt help.

Since, its huge database and i am not familiar with this. I request VBA macro codes for this action.

I got codes from form for vlookup the values between sheets, but i am looking for workbooks, is it possible to help.

Codes for your reference.

Code:
Sub DemoA()
    Application.ScreenUpdating = False
    R& = Sheet1.UsedRange.Rows.Count - 1
For C& = 2 To Worksheets.Count
    With Sheet1.Cells(2, C).Resize(R)
        .Formula = "=VLOOKUP($A2," & Worksheets(C).Cells(1).CurrentRegion.Columns(13).Address(, , , True) & ",1,FALSE)"
        .Formula = .Value
    End With
Next
    Application.ScreenUpdating = True
End Sub
Thanks,
Guna.
 
Last edited by a moderator:
Top