Excel VBA applying formula problem

[kashif.special2005]

Hi,

In a large data I am applying formula in a column through vba, and it is taking lot of time to fetch data, please help me how can I optimize this code, so the code can run fast.

For i = 2 To 480000
    'Cells(i, 10).Select
    Cells(i, 10).Formula = "=vlookup(A2,'2G_NSC RF Data'!A:F,2,0)"
    If IsError(Cells(i, 10).Value) Then
        Cells(i, 10).Formula = "=vlookup(A2,'A_BTS'!A:J,6,0)"
    End If
    If IsError(Cells(i, 10).Value) Then
        Cells(i, 10).Formula = "=vlookup(A2,'2G All Vendor Dump'!A:J,8,0)"
    End If
    If IsError(Cells(i, 10).Value) Then
        If Cells(i, 3) = "" Then
            Cells(i, 3) = "Either Source/Target Cell Data Not Found"
        End If
    End If
Next i

Thanks
Kashif

 

[Beyond_avarice]

Wrap your code in the following:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

It appears your are populating an if statement and the checking to see if that result is an error. If so then you are populating a vlookup based on another sheet. and repeating this process through three different sheets.

I would suggest consolidating your data on a separate page and running one vlookup. Also it appears your for script is using almost a half million iterations and I wonder if you truly need that many. Can you add a variable that identifies how many iterations you will truly need?

 

[kashif.special2005]

Hi Beyond_avarice,

Thanks for reply, I like your idea about consolidating all the sheets data into one sheet, and then apply one vlookup.

Thanks
Kashif