Dynamically locate and copy last number in a column before #N/A error

Hi Experts,
Please I need help with VBA code to dynamically locate and copy the last values in column E before the errors and corresponding date in column A.

This is the amount highlighted in yellow i.e. cell E16 and in column A also highlighted in yellow i.e. cell A16 from tab "Q1" into tab "Summary". The values should be copied to cells C3 and C9 in the Summary tab of the same file.
In this instance the cell is E16 and A16 but this varies from customer to customer.
Below is a picture of tab Q1
https://www.mrexcel.com/board/attach...835-png.87265/

Welcome to the forum Tactful. A sample workbook is so much easier to understand rather than an image. Could you please supply a sample workbook with Q1 and Summary sheets?

Try using the code:

Code:

---

=If(or(E2= "",f2=""),"",E2-B2)

---

Maybe try this

Code:

---

Sub copyvalues ()
  Dim f As Range
  Dim i As Long
 
  With Sheets("Q1")
    Set f = .Range("E:E").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      .Range("A" & i & ","E" & i).Copy
      Sheets("Summary").Range("C3" & “C9” ).Paste
    End If
  End With
End Sub

---

Mind you, most of this code will look very familiar to you Tactful? I mean after all its from MrExcel where you were receiving help, that you failed to mention.

See attached and below for some Excel 365 options if you have 365?

Get whole row:

PHP Code:

---

=LET(data,A2:F25,i,MIN(IFERROR(BYCOL(data,LAMBDA(x,MATCH(TRUE,ISNA(x),0))),""))-1,DROP(TAKE(data,i),i-1)) 


---

Get only column E value:

PHP Code:

---

=INDEX(LET(data,A2:F25,i,MIN(IFERROR(BYCOL(data,LAMBDA(x,MATCH(TRUE,ISNA(x),0))),""))-1,DROP(TAKE(data,i),i-1)),,5) 


---

OR:

PHP Code:

---

=LET(f,FILTER(E2:E25,NOT(ISNA(E2:E25))),DROP(f,ROWS(f)-1)) 


---

Quote:

---

Originally Posted by Aussiebear

Mind you, most of this code will look very familiar to you Tactful? I mean after all its from MrExcel where you were receiving help, that you failed to mention.

---

@Aussiebear:
I was aware of your post above but i am still playing with the Excel 365 functions...