PDA

View Full Version : Help Vlookup!!



jumbel
09-11-2008, 08:08 AM
I am using vlookup to look for values from another workbook. I need to display those values in the same workbook as that of my macro. It works well when the First.xls file is open, but it takes a lot of time for executing just 6 lines of code. The code can be seen below.

Can anybody help me with this??


Sub Lookup()
Range("GF4:GF2000").Select
Selection.FormulaR1C1 = "=RC7&RC8&RC9"

Range("GG4:GG2000").Select
Selection.FormulaR1C1 = "=SUBSTITUTE(RC[-1],0,"""",1)"

Range( "J4:J1090). Select
Selection.FormulaR1C1 = _
"=VLOOKUP(R4C189:R1091C189,[First.xls]Sheet1!R3C11:R804C233,223,FALSE)"

End Sub

Bob Phillips
09-11-2008, 09:09 AM
You could remove the Selects, but I don't see much else to speed-up



Sub Lookup()
Range("GF4:GF2000").FormulaR1C1 = "=RC7&RC8&RC9"

Range("GG4:GG2000").FormulaR1C1 = "=SUBSTITUTE(RC[-1],0,"""",1)"

Range( "J4:J1090). FormulaR1C1 = _
"=VLOOKUP(R4C189:R1091C189,[First.xls]Sheet1!R3C11:R804C233,223,FALSE)"

End Sub

MaximS
09-11-2008, 02:00 PM
There are two things you can do:

1. Change formulas to values - should speed up a bit.

Range("GG4:GG2000").Formula = Range("GG4:GG2000").Value

but if you want to leave it as a formula then copy to temporary column
and then do vlookup

2. Temporary copy the content of First.xls to your workbook and then delete after using. Vlookup is always taking longer when done against other workbook.

Cyberdude
09-11-2008, 02:02 PM
You might try putting these lines at the beginning:


Application.ScreenUpdating = False
Application.Calculation = xlManual

then at the end use:


Application.Screenupdating = True
Application.Calculation = xlAutomatic


They sometimes make a world of difference for speed. :hi:

jumbel
09-16-2008, 04:30 AM
thnks all for your inputs....