Find last number in column that includes text and return value

[Chestnuttgirl98]

I need to find the last occurrence of a number in a column that will also have text in some of the cells. For example, the column is tracking monthly variance. In future months, there is no variance so the cell returns NA(). I need to find the last number above the first occurrence of NA(), because i need to use that value in a different calculation.

 

[JoeMo]

If the only errors in the column are #N/A type this UDF may help. Otherwise it will return the last number preceding the first error in the column.

Book1
	A	B	C
1	338		90
2	563
3	10
4	855
5	204
6	105
7	688
8	912
9	667
10	617
11	127
12	325
13	699
14	805
15	894
16	589
17	310
18	105
19	90
20	#N/A
21	634
22	768
23	884
24	#DIV/0!
25	315
26	165
27	944
28	#N/A
29	580
A

Cell Formulas
Range	Formula
C1	C1	=LastNumBeforeNA(A:A)
A20,A28	A20	=NA()
A24	A24	=1/B24

Function LastNumBeforeNA(R As Range) As Variant
Dim c As Variant, FirstNA As Variant
For Each c In R
If IsError(c) Then
    Set FirstNA = c
    Exit For
End If
Next c
If Not FirstNA Is Nothing Then
    Set R = Range(R.Cells(1, 1), FirstNA.Offset(-1, 0))
    LastNumBeforeNA = Evaluate("Index(" & R.Address & ",Match(9.99E307," & R.Address & "))")
Else
    LastNumBeforeNA = CVErr(xlErrNA)
    Exit Function
End If
End Function

 

[Rick Rothstein]

Here is another way to write the LastNumBeforeNA function...

Function LastNumBeforeNA(R As Range) As Variant
  LastNumBeforeNA = Cells(Evaluate("MIN(IF(ISNA(" & R.Address & "),ROW(" & R.Address & ")))") - 1, "A").Value
End Function

The above function will skip over any errors other than #N/A errors. The following function will give the last number before ANY error (if that turns out to be a desired functionality)...

Function LastNumBeforeAnyError(R As Range) As Variant
  LastNumBeforeAnyError = Cells(Evaluate("MIN(IF(ISERROR(" & R.Address & "),ROW(" & R.Address & ")))") - 1, "A").Value
End Function

Note: Like JoeMo's code, both of my code's above will return an error if cell A1 contains an error (because there is no cell before A1 so there is no value to retrieve).