formula help

[RattlingCarp3048]

I am using formula =IF(IFERROR(VLOOKUP())) in a macro which is working when it is initially applied to the data. However, i noticed when i add more rows and use the following code to copy the same formula down the rest of the column, for some reason certain cells are filled with #N/A as text while the rest have the formula applied. I cannot find any difference in the cells with #N/A as text.

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("V2:X" & lastRow).SpecialCells(xlCellTypeBlanks).Formula = Range("V2:X2").Formula

Note: if i manually copy the formula down, the cells do update with the correct formula and the error goes away. the rest of the macro before this line is working perfectly.

 

[6StringJazzer]

You need to show us the actual formulas that are in V2:X2 if we have any hope of helping.

 

[RattlingCarp3048]

You need to show us the actual formulas that are in V2:X2 if we have any hope of helping.

V=IF(IFERROR(VLOOKUP(U2,'Yesterday''s Report'!U:V,2,0),"")="","",VLOOKUP(U2,'Yesterday''s Report'!U:V,2,0))
W=IF(IFERROR(VLOOKUP(U2,'Yesterday''s Report'!U:W,3,0),"")="","",VLOOKUP(U2,'Yesterday''s Report'!U:W,3,0))
X=IF(IFERROR(VLOOKUP(U2,'Yesterday''s Report'!U:X,4,0),"")="","",VLOOKUP(U2,'Yesterday''s Report'!U:X,4,0))

This formula removes the 0 and errors that the vlookup returns which is working perfectly for 97% of the 3 columns. I simply cannot understand why the code will not apply the formulas to select cells.

at start up the user will run the macro for the first time which applies the formulas amungst other items. that works perfectly. However, later in the day we add on more data and run a second macro that does many other tasks which ends with copying the now existing formulas all the way down to the last row. The cells are blank prior to the formulas being copied down. They are formatted as general text (i did try try to change it to text but no change). There is data for the vlookup to return that is also formatted as general text. If i ignore the macro and flash fill the formulas down manually the return value is correct. Why is the macro selecting only certain cells and inserting #N/A as text instead of the formula?

 

[6StringJazzer]

What exactly do you want your formula to do? If VLOOKUP returns an error, then you are using IFERROR to set the result to the null string. However, you have an IF wrapped around that so that if the result if the null string, you are repeating the VLOOKUP again. So you are guaranteeing that if there is an error, you will return the error. That is probably what is happening in the cases where you are getting #N/A however, it is impossible for me to know for certain without seeing all the data.

Is it acceptable to simply return the null string in the cell if VLOOKUP returns an error? If so then you want just the first part of your formula:

=IFERROR(VLOOKUP(U2,'Yesterday''s Report'!U:V,2,0),"")

I suggest you fix the formulas first and see what results you get.

 

[RattlingCarp3048]

What exactly do you want your formula to do? If VLOOKUP returns an error, then you are using IFERROR to set the result to the null string. However, you have an IF wrapped around that so that if the result if the null string, you are repeating the VLOOKUP again. So you are guaranteeing that if there is an error, you will return the error. That is probably what is happening in the cases where you are getting #N/A however, it is impossible for me to know for certain without seeing all the data.

Is it acceptable to simply return the null string in the cell if VLOOKUP returns an error? If so then you want just the first part of your formula:

=IFERROR(VLOOKUP(U2,'Yesterday''s Report'!U:V,2,0),"")

I suggest you fix the formulas first and see what results you get.

taking out the if statement worked, which is interesting because i have used that same formula pattern on other documents without issues.

This formula was originally build with another document and modified for this current one. I started with the vlookup() but was returning 0 and #N/A, i wanted a formula to return the value of the vlookup or blank. So I then added the iferror() with removed the #N/A but it left the 0, which then led me to use the if() to remove the 0. so the if(iferror(vlookup())) combo works great for all of my other documents where it is in use. Except this one. taking out the if() worked and did not leave any 0.

Ill research more on the why behind it all but thanks!!