Windows XP Slow Lookup

[Zade]

Help!!!!!!!!!!!!!!!!!!!!!!!!!!!Help

Hello All,

I'm wondering if anyone can help me out with the code below. When i run it it takes very very long time to run. i would appreicate your help. The data size is very large (more than 50k rows and over 24 clos.)


'***Declarations
Dim iRowPlan As Integer
Dim iRowLTable As Integer
Dim iLastRowPlan As Integer
Dim iLastRowTable As Integer
'***Code
' Select the lookup table (LTABLE)Worksheet
Sub Vlkup()
Sheets("LTABLE").Select

iLastRowTable = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row

' Select the worksheet containing the Plan(PLAN)
Sheets("PLAN").Select
' Find the number of Planrecords and store it in iLastRow
iLastRowPlan = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row

' Loop through the Plantable and compare each record with Plan in the lookup table
' If a match is found, change the Plantable with what is in the lookup table
For iRowPlan = 6 To iLastRowTable
For iRowLTable = 2 To iLastRowPlan
If Sheets("PLAN").Cells(iRowPlan, 1) = Sheets("LTABLE").Cells(iRowLTable, 1) _
Then
Sheets("EssPlan").Cells(iRowPlan, 1) = Sheets("LTABLE").Cells(iRowPlan, 2)
Sheets("EssPlan").Cells(iRowPlan, 2) = Sheets("LTABLE").Cells(iRowLTable, 3)
Sheets("EssPlan").Cells(iRowPlan, 3) = Sheets("LTABLE").Cells(iRowLTable, 4)
Sheets("EssPlan").Cells(iRowPlan, 4) = Sheets("LTABLE").Cells(iRowLTable, 5)
Sheets("EssPlan").Cells(iRowPlan, 5) = Sheets("LTABLE").Cells(iRowLTable, 6)
Sheets("EssPlan").Cells(iRowPlan, 6) = Sheets("LTABLE").Cells(iRowLTable, 7)
Sheets("EssPlan").Cells(iRowPlan, 7) = Sheets("LTABLE").Cells(iRowLTable, 8)
Sheets("EssPlan").Cells(iRowPlan, 8) = Sheets("Plan").Cells(iRowPlan, 11)
Sheets("EssPlan").Cells(iRowPlan, 9) = Sheets("Plan").Cells(iRowPlan, 12)
Sheets("EssPlan").Cells(iRowPlan, 10) = Sheets("Plan").Cells(iRowPlan, 13)
Sheets("EssPlan").Cells(iRowPlan, 11) = Sheets("Plan").Cells(iRowPlan, 14)
Sheets("EssPlan").Cells(iRowPlan, 12) = Sheets("Plan").Cells(iRowPlan, 15)
Sheets("EssPlan").Cells(iRowPlan, 13) = Sheets("Plan").Cells(iRowPlan, 16)
Sheets("EssPlan").Cells(iRowPlan, 14) = Sheets("Plan").Cells(iRowPlan, 12)
Sheets("EssPlan").Cells(iRowPlan, 15) = Sheets("Plan").Cells(iRowPlan, 13)
Sheets("EssPlan").Cells(iRowPlan, 16) = Sheets("Plan").Cells(iRowPlan, 14)
Sheets("EssPlan").Cells(iRowPlan, 17) = Sheets("Plan").Cells(iRowPlan, 15)
Sheets("EssPlan").Cells(iRowPlan, 18) = Sheets("Plan").Cells(iRowPlan, 16)
Sheets("EssPlan").Cells(iRowPlan, 19) = Sheets("Plan").Cells(iRowPlan, 17)
Sheets("EssPlan").Cells(iRowPlan, 20) = Sheets("Plan").Cells(iRowPlan, 18)
Sheets("EssPlan").Cells(iRowPlan, 21) = Sheets("Plan").Cells(iRowPlan, 19)
Sheets("EssPlan").Cells(iRowPlan, 23) = Sheets("Plan").Cells(iRowPlan, 20)
Sheets("EssPlan").Cells(iRowPlan, 24) = Sheets("Plan").Cells(iRowPlan, 21)
Exit For
End If
Next iRowLTable
Next iRowPlan
End Sub

 

[Anon_F]

You may have to wait a while for help so please be patient.

 

[Ian]

This is way beyond my excel knowledge unfortunately, you may need to try a forum specialising in excel to get a quick answer.