VBA/formula help

[RattlingCarp3048]

I have this spreadsheet that just keeps on circling back with problems, hopefully you guys can help once again....

It is updated with new data multiple times throughout the day so the last row is ever changing. the data is in A-Q with formulas going into R-X. Placement of the formulas in V-X is the issue.

When the spreadsheet is updated i need to find the next available row in column V-X and place the formulas. How can i find the next available row in V-X?

if I use lastrow = Cells(Rows.Count, "V").End(xlUp).Offset (1).Row is shows the next row = 9001 but its not, in this update the next cell in column V-X is 8993

 

[Joe4]

Can you post exactly what formulas are in V2, W2, X2, and Y2?

If I see that those formulas are, I think we may be able to incorporate it into what you are trying to do with your previous code attempt.

 

[RattlingCarp3048]

Can you post exactly what formulas are in V2, W2, X2, and Y2?

If I see that those formulas are, I think we may be able to incorporate it into what you are trying to do with your previous code attempt.

They are all vlookup's nested with If() and iferror() to remove the return value of 0 and blanks. The formulas alone work perfectly on their own when the initial macro is ran. the problems start occurring when the update is performed and the formulas need to be filled down.

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

W2 =IFERROR(IF(VLOOKUP(U2,'Yesterday''s Report'!U:W,3,0)=0,"",VLOOKUP(U2,'Yesterday''s Report'!U:W,3,0)),"")

X2 =IFERROR(IF(VLOOKUP(U2,'Yesterday''s Report'!U:X,4,0)=0,"",VLOOKUP(U2,'Yesterday''s Report'!U:X,4,0)),"")

Y2 =IFERROR(IF(VLOOKUP(U2,'Yesterday''s Report'!U:Y,5,0)=0,"",VLOOKUP(U2,'Yesterday''s Report'!U:Y,5,0)),"")

 

[Joe4]

Try this:

'   Find last row in column U with data
    lastrow = Cells(Rows.Count, "U").End(xlUp).Row
    
'   Populate empty cells with appropriate formulas
    Range("V2:V" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-1],'Yesterday''s Report'!C[-1]:C,2,0)=0,"""",VLOOKUP(RC[-1],'Yesterday''s Report'!C[-1]:C,2,0)),"""")"
    Range("W2:W" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-2],'Yesterday''s Report'!C[-2]:C,3,0)=0,"""",VLOOKUP(RC[-2],'Yesterday''s Report'!C[-2]:C,3,0)),"""")"
    Range("X2:x" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-3],'Yesterday''s Report'!C[-3]:C,4,0)=0,"""",VLOOKUP(RC[-3],'Yesterday''s Report'!C[-3]:C,4,0)),"""")"
    Range("Y2:Y" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-4],'Yesterday''s Report'!C[-4]:C,5,0)=0,"""",VLOOKUP(RC[-4],'Yesterday''s Report'!C[-4]:C,5,0)),"""")"

The key is to use the relative reference (R1C1) formula format when entering your formulas.
The formulas may look a little crazy, but the good news is that you don't even need to understand that in order to get the formula in that format.
If you turn on the Macro Recorder, and record yourself entering the appropriate formula in each column, and then stop the Macro Recorder and view your code, you will have the exact code you need for those formulas and can simply copy/paste it into your other code (just like I did already for you above).

 

[RattlingCarp3048]

Try this:

'   Find last row in column U with data
    lastrow = Cells(Rows.Count, "U").End(xlUp).Row
   
'   Populate empty cells with appropriate formulas
    Range("V2:V" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-1],'Yesterday''s Report'!C[-1]:C,2,0)=0,"""",VLOOKUP(RC[-1],'Yesterday''s Report'!C[-1]:C,2,0)),"""")"
    Range("W2:W" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-2],'Yesterday''s Report'!C[-2]:C,3,0)=0,"""",VLOOKUP(RC[-2],'Yesterday''s Report'!C[-2]:C,3,0)),"""")"
    Range("X2:x" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-3],'Yesterday''s Report'!C[-3]:C,4,0)=0,"""",VLOOKUP(RC[-3],'Yesterday''s Report'!C[-3]:C,4,0)),"""")"
    Range("Y2:Y" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
        "=IFERROR(IF(VLOOKUP(RC[-4],'Yesterday''s Report'!C[-4]:C,5,0)=0,"""",VLOOKUP(RC[-4],'Yesterday''s Report'!C[-4]:C,5,0)),"""")"

The key is to use the relative reference (R1C1) formula format when entering your formulas.
The formulas may look a little crazy, but the good news is that you don't even need to understand that in order to get the formula in that format.
If you turn on the Macro Recorder, and record yourself entering the appropriate formula in each column, and then stop the Macro Recorder and view your code, you will have the exact code you need for those formulas and can simply copy/paste it into your other code (just like I did already for you above).

yes, using the macro recorder is how i formatted them initially to help build the code. i think my confusion here is when we update the spreadsheet the formulas are already in. today for example, the initial run we will do at 2pm will apply the formulas and extend down to from row 2-11640 (tomorrow will be a different last row).

every 2-3 hours after that we will add more data. the formulas in 11640 will need to be extended down to the last row. that the is the actual problem area. when the macro is autofilling down

 

[Joe4]

As long as you can look at column U to determine where the last row of data is, it should not matter how many times you try to run it in a day, it should always work based on the current data that is in there.

Have you tried out the code I gave to see if it does this?

 

[Joe4]

Are you looking for something dynamic, something that you do not have to manually run, and automatically populates those formulas as new data is added?
If so, how exactly is new data added?
Is it done manually, line-by-line, or is it some sort of copy/paste or import?

 

[RattlingCarp3048]

As long as you can look at column U to determine where the last row of data is, it should not matter how many times you try to run it in a day, it should always work based on the current data that is in there.

Have you tried out the code I gave to see if it does this?

yes, sorry, i was running it multiple times to see if it would return the same results.

your format worked to keep the rows sequential within the formula which was a big problem now resolved.

at the moment im not getting any N/A's or 0 being returned from the vlookups which is good. Unfortunately i'll have to test with the night shift as those users are the ones that keep having the N/A's returned (not sure why just them and no one else when its the same code)

i also dont see any over-writing either on any of my tests, again though night shift are the users that keep having this issue. Ill have to circle back around tomorrow and double check on these two issues.

 

[Joe4]

i also dont see any over-writing either on any of my tests

As you shouldn't, since we are purposely using "SpecialCells(xlCellTypeBlanks)" to only select the empty cells.
The only thing it should be doing is put the correct VLOOKUP formula in the blank cells in your used range.

If you are seeing anything else, I suspect your users maybe doing things you are expecting (moving cells around, inserting/deleting cells, etc).
So if your night users come across any issues, your first step should probably be to find out from them exactly what they did when this situation happens ("operator error" is a common cause for these kind of issues).

 

[RattlingCarp3048]

Are you looking for something dynamic, something that you do not have to manually run, and automatically populates those formulas as new data is added?
If so, how exactly is new data added?
Is it done manually, line-by-line, or is it some sort of copy/paste or import?

the sheet as a whole functions 1/2 manual and 1/2 auto.

the primary users manually add in a set of raw data then click a button to run the macro. The macro then compares the data, reformats, removes duplicates, removes line items that are older than set criteria, adds in the formulas, and will auto date/time stamp each line item via a change event as the users check off each item completed.

periodically they will add in a new set of raw data and push the update which will then reformat the raw data into the same form as the final report and pull the now existing formulas down to the new last row. new data is added several times throughout the day/night so the update is pushed anywhere from 7-12 times depending on the workload coming in.

its a very large file with a ton of steps to it that we used to do 100% manual, took way to long to do, and no 2 people were doing it the same despite very clear and laid out instructions.

 

[RattlingCarp3048]

As you shouldn't, since we are purposely using "SpecialCells(xlCellTypeBlanks)" to only select the empty cells.
The only thing it should be doing is put the correct VLOOKUP formula in the blank cells in your used range.

If you are seeing anything else, I suspect your users maybe doing things you are expecting (moving cells around, inserting/deleting cells, etc).
So if your night users come across any issues, your first step should probably be to find out from them exactly what they did when this situation happens ("operator error" is a common cause for these kind of issues).

I think a lot of it does have to do with operator error as well, unfortunately they have been trained and retrained numerous times and still have complications. Thats part of why the code as a whole is so much more complicated than it needs to be. for now im calling it a win and will have to circle back around in a day or so to confirm if any other adjustments are needed.

in the mean time... question... since the formulas are already in when the update is pushed why would the original line of coding not work to copy the formulas down when is seems to be very very similar to what your version is? your version is putting in the formulas again and pulling them down individually where the original was taking the existing formulas and pulling them all down together. i dont understand what the significant difference is to cause the issues i was experiencing.

original:
'Fill down formulas in Columns V-X
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("V2:Y" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2:Y2").FormulaR1C1