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]

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

1. Your lastrow is looking at column "A", not column "U". They might both end in the same place, but maybe not (I know nothing about your data, so I cannot say).
2. I don't think you can do all columns and formulas at the same time like that. Think about it. If you have a blank cell in cell Y8, you are then trying to put 4 columns worth of formulas in that single column! It doesn't really make sense.

You would need to do each column separately, like I did, i.e. for column V

Range("V2:V" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2").FormulaR1C1

and then repeat for the other three columns.

 

[RattlingCarp3048]

1. Your lastrow is looking at column "A", not column "U". They might both end in the same place, but maybe not (I know nothing about your data, so I cannot say).
2. I don't think you can do all columns and formulas at the same time like that. Think about it. If you have a blank cell in cell Y8, you are then trying to put 4 columns worth of formulas in that single column! It doesn't really make sense.

You would need to do each column separately, like I did, i.e. for column V

Range("V2:V" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = Range("V2").FormulaR1C1

and then repeat for the other three columns.

ok so its been a couple of days. so all of the original issues have been corrected. No more N/A's, no more over-writing, and the formulas are populating correctly. However, there is a new issue that was not present before.

Column Y the code populates the formula then copies it down. that is now working. However, there is also a change event assigned to that column to over-write the formula and populate the date/time stamp. it has been working fine for the last month without issues but now its not. it says if the user changes column V, then populate date/time in column Y. it seems to have a 50/50 chance of working. i assume this is an after effect from correcting the formula issues. maybe i have something wrong in the code?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 22 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 3) = Format(Now(), "mm-dd-yyyy hh:mm")
Application.EnableEvents = True
End If
Handler:
End Sub

 

[Joe4]

Why do you seem to have two totally unrelated things happening in column Y?

Back in post 12, you said that there should be a formula in column Y that looks like this:

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

but now you are saying that there is a Worksheet_Change event procedure code automatically populating column Y with a date/time stamp.

If that is the case, what is the purpose of placing that formula in column Y if it is only going to be overwritten immediately?
It seems to me that the solution is to remove the section of code that we created that that is populating column Y, since it is just going to be overwritten by the date/time stamp anyway.

 

[RattlingCarp3048]

Why do you seem to have two totally unrelated things happening in column Y?

Back in post 12, you said that there should be a formula in column Y that looks like this:

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

but now you are saying that there is a Worksheet_Change event procedure code automatically populating column Y with a date/time stamp.

If that is the case, what is the purpose of placing that formula in column Y if it is only going to be overwritten immediately?
It seems to me that the solution is to remove the section of code that we created that that is populating column Y, since it is just going to be overwritten by the date/time stamp anyway.

the report contains a portion of old data and a portion of new data. Column Y formula is correct, it is returning the date stamp for the cases from yesterday but still need to date/time stamp the new cases that are coming in today.

 

[Joe4]

So, what do you want to happen in this scenario?
Do you want it to keep the formula that the code is trying to put in?
Or do you want it put in a new date/time stamp?

If you want the new date/time stamp, then do as I said in the last post, and remove the part of our new code that is populating column Y.

If you want it to keep the formula the new code is putting in column Y, then we need to prevent the automated Worksheet_Change code from running while we do these updates.
We can do that by temporarily disabling events while those updates happen, i.e.

'   Temporarily disable events
    Application.EnableEvents = False

'   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)),"""")"

'   Re-enable events
    Application.EnableEvents = True

 

[RattlingCarp3048]

So, what do you want to happen in this scenario?
Do you want it to keep the formula that the code is trying to put in?
Or do you want it put in a new date/time stamp?

If you want the new date/time stamp, then do as I said in the last post, and remove the part of our new code that is populating column Y.

If you want it to keep the formula the new code is putting in column Y, then we need to prevent the automated Worksheet_Change code from running while we do these updates.
We can do that by temporarily disabling events while those updates happen, i.e.

'   Temporarily disable events
    Application.EnableEvents = False

'   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)),"""")"

'   Re-enable events
    Application.EnableEvents = True

yes, correct. we need to keep the formula in Y when that module runs and only run the worksheet change code after. I didn't even think about the change event trying to run simultaneously. Ill add that in today and see what it looks like tomorrow. this issue never showed up in any of my tests, or at least i didnt notice it.

 

[Joe4]

this issue never showed up in any of my tests, or at least i didnt notice it.

It was probably happening, though code be inconsistent, since the code is updating columns W, V, X, Y in rapid succession, but updates to column V trigger that code to run in column Y.
So there are two things updating column Y at almost the same time. Which one happens first might depend on the speed of the macro at that point in time (in which there can be slight variations, depending on what else is using system resources at that moment in time).