Incorrect calculation of Vlookup value when running Macro

**Kreef** · 06-25-2011, 08:22 AM

Hi

I am running a macro that, among many thing, has to vlookup values for a massive table (about 60,000 cells to calculate).

After running the macro, I saw that some values had calculated to #N/A even though there were definitely values for their indexes to display. Afterwards I double and triple checked that these values should not have displayed as #N/A.

I then went through the code line by line and this time all the values calculated 100% correct.

My question:
Is it possible that the macro is continuing with the next line of code while all the cells have not completed calculating their Vlookup values and hence certain cells then display #N/A?

If this is the case, is there a way that I can put a 'pause' after the vlookup to give Excel enough time to calculate all the cells?

**arthurbr** · 06-25-2011, 08:41 AM

Hi and welcome to the board.
I suggest you post your macro so that the VBA guys can have a look at it

**Kreef** · 06-25-2011, 09:12 AM

Thanks for the welcome.
Even though the code is VERY simple and VERY untidy, I don't think the problem is with the code. But here it is anyways.

First it puts the formulas in an empty row. This row then obviously calculates all the values to #N/A

Range("N35001").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-1],""NO"",""YES"")"
    Range("O35001").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,13,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,13,FALSE))"
    Range("P35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,14,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,14,FALSE))"
    Range("Q35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,15,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,15,FALSE))"
    Range("R35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,16,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,16,FALSE))"
    Range("S35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,17,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,17,FALSE))"
    Range("T35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,18,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,18,FALSE))"
    Range("U35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,19,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,19,FALSE))"
    Range("V35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,20,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,20,FALSE))"
    Range("W35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,21,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,21,FALSE))"
    Range("X35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,22,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,22,FALSE))"
    Range("Y35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,23,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,23,FALSE))"
    Range("Z35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,24,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,24,FALSE))"
    Range("AA35001").Select
    ActiveCell.FormulaR1C1 = ""
    Range("AB35001").Select
    ActiveCell.FormulaR1C1 = "=IF(VLOOKUP(RC3,Prev_Orderwell!C1:C26,26,FALSE)=0,"""",VLOOKUP(RC3,Prev_Orderwell!C1:C26,26,FALSE))"

I then copy the formulas that have been entered into row 35,001 and paste it in the area where I need the values. This area consists of several rows that have been filtered using Autofilter. I thus only paste it into the visible rows.

It then calculates for quite some time. After this I delete the formulas from row 35,001

    Range("L35001:AB35001").Select
    Selection.Copy
    Range("L2:AB" & vLastRow).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.Paste
    Rows("35001:35001").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

I make all rows visible again. Select all cells and Special paste them as values to remove the formulas.

    
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

At the end of all of this, the area contains the following:

Rows were columns L to AB are empty (this is correct)

Rows were columns L to AB contain the values that were on sheet Prev_Orderwell (this is correct)

Rows were columns L to AB contain #N/A where the index in column C did NOT have any corresponding data in Prev_Orderwell (this is correct)

Rows were columns L to AB contain #N/A where the index in column C DID have corresponding data in Prev_Orderwell (MY PROBLEM)

When I paste the formulas from row 35,001 into the area, the values that are brought with the formulas is #N/A. It seems that not all the rows are getting time to recalculate before I copy the entire area and pastes it as values.

Any thoughts?

**royUK** · 06-25-2011, 08:59 AM

Why are using VLOOKUP in the code?

**Kreef** · 06-25-2011, 09:13 AM

Originally Posted by royUK

Why are using VLOOKUP in the code?

Because I am still a beginner and I do not know how else to do it. It is working for the purpose that I need it, but it is not working on all the rows...