Macro's for Vlookup

[Ravi prasad]

Hi,

I've macro for vlookup. However, when i run below macros its taking lot of time to update.

I think because of the range A2:A4999 and R1C1:R4999C20.

I'm not aware of the last row(end row) in which data will be so, i have given the pre-defined range A2:A4999.

Because of this range i think its running very slow.

Can any one help me with end row.


Sub ADDCLM()
Dim ctr, Table1, Table2, cl
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
ctr = 0
Worksheets("SHEET1").Activate
Table1 = Sheet1.Range("A2:A4999") ' Employee_ID Column from Employee table
Table2 = Worksheets(Sheet2).Range("A2:T4999") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("R2").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("R2").Column
For Each cl In Table1
'Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-18], R2C8:R13C9, 18, False)"
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC1,'[Property Tax Opportunities (Previous).xlsx]Sheet1'!R1C1:R4999C20, 18,False)"
Dept_Row = Dept_Row + 1
ctr = ctr + 1
Next cl
'MsgBox "Done"
End Sub


Regards,
Ravi

 

[CodeNinja]

Ravi,
Having that many formulas will slow down your excel no matter what you do.... you can speed things up by doing 2 things... First, turn screen updating off (application.screenupdating = false). Second instead of looping through each cell and entering the formula, how about using autofill:

Sub ADDCLM()
 Dim Table1 As Range
 Application.ScreenUpdating = False

 Worksheets("SHEET1").Activate
 Set Table1 = Sheet1.Range("A2:A4999") ' Employee_ID Column from Employee table
 Sheet1.Cells(2, 1) = "=VLOOKUP(A1,'[Property Tax Opportunities (Previous).xlsx]Sheet1'!A1:T4999, 18,False)"
 Sheet1.Range("A2").AutoFill Destination:=Table1, Type:=xlFillDefault
 Application.ScreenUpdating = True
 End Sub

Hope this helps...

CN.

 

[Ravi prasad]

Hi,

Thanks for the reply. However, i have used your macro but i'm not able to get any results.

Regards,
Ravi

 

[CodeNinja]

please provide sample of data and desired result so I can adjust code accordingly.

CN.

 

[Ravi prasad]

Hi,

Sorry for the delay in response.

As per your request i'm sending sample files.


https://app.box.com/s/cwhp832sc58sfo3srk9jokjpcglk7oww


https://app.box.com/s/5z9cimoo3xbqb191oa5scygfzthg4fik

Please click on the links and download the respective files.

Requirement: I trying to run Macro(VLOOKUP) inlieu of formula. In "Property Tax Opportunities (Currentt)" under column R, S and T i written the formula for your understanding.

In lieu of Formula i want Macro.

thanks
rv

 

[CodeNinja]

Ravi,
Sorry, as a general rule, I do not upload files. Please post an example in the body of this forum if you would like me to take a look at it.

CN.

 

[SpillerBD]

Screen updating makes a difference but turn off calculations too, that'll be bigger time savings.
I have 30k ~ 5-k rows with multiple index-match and a big array.... I just walk away when it calculates.