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

[Tactful]

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/attachments/1678483992835-png.87265/

[Aussiebear]

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?

[Daxton A.]

Try using the code:


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

[Aussiebear]

Maybe try this


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.

[georgiboy]

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

Get whole row:

=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:

=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:

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

[georgiboy]

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...