Improvising a vlookup code

[VKS]

Dear Experts,
Code below takes about 7.5 mins to fill up the formula and then converting values in the cells
(Total number of columns rows used in this sheet is 999,999 (which can vary).
Converting to values is required as sheet 2 will be deleted after few steps.
Please consider my VBA knowledge as zero as I am learning VBA through net and this forum
Best Regads/VKS

Please Login or Register  to view this content.

[coreytroy]

What determines the Activecell address?
Avoid selecting the cells to speed up the process.

[Andrew-R]

Doing such huge VLOOKUPs is always likely to be slow, but you could switch to using IFERROR, which would mean that each VLOOKUP only had to be performed once instead of twice:

Please Login or Register  to view this content.

[RHCPgergo]

Maybe you could use the Find method in VBA instead of VLOOKUP?

[Andrew-R]

That would be much slower, as the macro would have to loop through all of the cells being written and run the .Find for each one.

[VKS]

What determines the Activecell address?
Avoid selecting the cells to speed up the process.

Thanks Coreytroy
range("a1").Select
Selection.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.End(xlDown).Offset(0, -1).Select
Selection.End(xlUp).Offset(0, 1).Select

[VKS]

@ Andrew: wowwwww its 1:58 mins now and you already have my star for your help