VBA and VLOOKUP

[A_Al]

Hello,
Is there a VBA macro that would allow me to match the data in Sheet1 with Sheet2 as a VLOOKUP formula?
I tried the below code but it takes too long/slow:

Private Sub CommandButton1_Click()
For I = 2 To 573168
Worksheets("Sheet1").Cells(I, 31).Value = Application.VLookup( _
Worksheets("Sheet1").Cells(I, 6).Value, Worksheets("Sheet2").Range("A:B"), 2, 0)
Next

I have attached an example of my request, please note that the file contains more than 500,000 rows.

 

[Marc L]

Hi,​

​

yes as looping cell-by-cell is slower than applying a formula directly in all the range !​

​

So why don't you go to the formula way ?​

 

[A_Al]

Thanks Marc.
The formula takes too long, I want a faster way if possible