Vlookup issues?

[zabiullakhan]

I have a vlookup formula as below

ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0)),"""",VLOOKUP(RC[" & TCDLVal & "],RRng,2,0))"

It takes 10 minutes to get the results with this formula for 47000 rows and sometimes my system is geting hanged..

Specification of my system is prety good - i have Intel Core i5 processor and 3gb ram and i am using excel 2007.

Please suggest me a solution to get this a bit faster and avoide system freezz.

 

[ukmikeb]

Hi

Use the more compact formula for the version of the product!

Please suggest me a solution to get this a bit faster and avoide system freezz.

Have the courtesy to acknowledge assistance given in previous threads.

 

[AlphaFrog]

This should be a little faster...

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"

IFERROR function


If you are looping to fill that formula into 47000 cells, it could be a lot faster to use the .Autofill method or to apply the formula to all the cells at once using reletive cell references.

This is an example using .Autofill.

Range("A1").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
Range("A1").Autofill Destination:=Range("A1:A47000")

 

[zabiullakhan]

Thanks Guys.. However can i have a piece of code how to loop it and make it faster..

Please help

 

[zabiullakhan]

I have added in a loop but how do i use AutoFill, Please suggest.

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
    
For Row = 2 To tgp_lastrow
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
    Application.Calculate
        Selection.Copy
        Selection.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next Row

 

[AlphaFrog]

Replace all your code above with just this...

ActiveCell.Resize(tgp_lastrow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
ActiveCell.Resize(tgp_lastrow).Value = ActiveCell.Resize(tgp_lastrow).Value

 

[zabiullakhan]

this seem's to have an defect.. it give the result for few and leave "0" for many rows.. i guess the calculations are not happening..

Any other way. can we first calculate and then paste the values..

 

[AlphaFrog]

I would guess the formula is not correct. It's calculating for each cell it is in, but for some reason the formula is returning zero for some cells. I don't know enough about what you are doing and what result you want to fix the formula.

The 1st line of code puts the formulas in the cells
The 2nd line of code replaces the formulas with the calculated values

If you comment out the 2nd line, you can then investigate why some of the formulas return zero.

 

[zabiullakhan]

This code work from the starting rows yeilds the result in no time for about 5 thousand rows after that it populates only "0"

ActiveCell.Resize(tgp_lastrow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
ActiveCell.Resize(tgp_lastrow).Value = ActiveCell.Resize(tgp_lastrow).Value

I tried the code below it yields results to all the 47 thousand rows but takes 14 minutes.

ActiveCell.Resize(tgp_lastrow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0),"""")"
[COLOR=red]Application.Calculate
[/COLOR]ActiveCell.Resize(tgp_lastrow).Value = ActiveCell.Resize(tgp_lastrow).Value

Please help

 

[AlphaFrog]

The two lines of code (without the Application.Calculate) work for me. I was able to VLookup 47000 cells in about 2.5 minutes on my old computer using Excel 2003. Your results should be much faster.

Maybe try using the old style IF(ISERROR formula instead of IFERROR

ActiveCell.Resize(tgp_lastrow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[" & TCDLVal & "],RRng,2,0)),"""",VLOOKUP(RC[" & TCDLVal & "],RRng,2,0))"

...though I doubt that's the problem and it's probably slower. I don't have Excel 2007 to compare with the IFERROR speed.

Also, instead of using ActiveCell, you can reference a cell directly using for example
Range("A1").Resize(tgp_lastrow).FormulaR1C1 =...
or
Range("A1:A" & tgp_lastrow).FormulaR1C1 =...

Can I ask why you are doing 47000 Vlookups? If you explain your situation, perhaps there is a better method than using a Vlookup formula.