Vba code to vlookup the long list in excel

Dear Expert,

I have a long list of data where i need VBA to help finding the related information.

I have two worksheets (Sheet1 and Sheet2)

On Sheet1
I am keying the number on Cell A1 and keep on keying under A2...A3..A4....until many (it can cross 40000 cells)

On Sheet2
I have a data in F1 AND G1 so on... until last end of the rows..
example:
G1 will be - HANO201217526895
F1 will be the short number of G1 which is brought with formula = Right(G1,6) (i.e. 526895)


So on sheet1, result required to be placed on C1. By matching A1 of Sheet1 with G1 of sheet2
Result : G1 of sheet2 to be avaialble in C1 of Sheet1

Since it is a huge data for vlookup, need a code where it should not slown down the excel.

Thanks

Sheet1(A1) will be identical to Sheet2(F1), which will bring the result in Sheet1(C1) from Sheet2(G1)

Generally speaking, its practically impossible to write VBA code thats faster than built in functions. Many factors contribute to this, one of them being VBA is not threaded and built in functions are. Thats an over simplification of why, but is a practical explanation.

There are things you can do to drastically speed up formulas. Sorting data is one easy to do thing that can help make lookups much faster. If what you are pulling over is numeric and not a string, a variation of SUMIF/SUMIFS would likely be much faster than a lookup. If you are referencing full columns in your VLOOKUP's, changing to reference only your data can drastically reduce calc time.

A sample file would better help evaluate this.

It is also a matter of relative time to complete the task. Slow down is a subjective concept. If your doing 40,000 look ups and it slows down for 3 seconds...thats actually reasonable. If it takes 15 mnutes that may not be. So exactly how slow is it now? Is it functional and calculating and it takes x minutes to finish or is the issue something else?

pLEASE FIND ATTACHMENT. THIS IS JUST SAMPLE AS I HAVE HUGE MORE THAN 40000 numbers

Currently each cell is built with VLOOKUP formula. When i open the excel it takes a time to open and for search it takes a time.
.
Please support in giving the code, will check how much time it takes.
.
Thanks

Originally Posted by anuwers

pLEASE FIND ATTACHMENT. THIS IS JUST SAMPLE AS I HAVE HUGE MORE THAN 40000 numbers

Ok, was the sample created from scratch or was it created by sanitizing your actual file? If it was created from scratch it may not be possible to see underlying factors that impact your actual file.

I also do not see your VLOOKUP in the sample, so I cannot speak to the possibility of improving the formula.

Again, VBA is unlikely to be the solution for you if a vlookup does what you need and is subjectively slow to you.

Originally Posted by anuwers

Currently each cell is built with VLOOKUP formula. When i open the excel it takes a time to open and for search it takes a time.
.
Please support in giving the code, will check how much time it takes.
.
Thanks

Open and close time may have nothing to do with the formula other than if you have automatic calculation turned on. Try this, toggle to manual calc, save/close/reopen. If it opens immediately then its calc time and that may be something we can address with the formula if you provide an example based on your actual file, sanitized.

If turning manual calc on doesnt improve the situation, then there is some aspect of your file causing it to be slow other than a formulas. We would have to inspect the file to find the culprit.

Is your actual file just 1 sheet with 40k rows? if so how many columns?

If its multiple sheets, how many? Do they also have alot of data?

Whats the size of the file (how many KB, MB, etc)?

thanks a lot it is working. But if i copy paste the list of numbers in A1 of sheet1, it is not working.